﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RN.ERP.Entity.ZBDLRY;
using RN.ERP.DAL.Context;
using RN.ERP.Entity;

namespace RN.ERP.DAL.ZbdlryDAL
{
    public class StatisticsDAL
    {
        private ZBDL_Context dbContext = new ZBDL_Context();
        
        public IList<ScoreAddSumByDepartmentNew> GetData(string AreaCode, DateTime StartDate, DateTime EndDate, string CorpTypeValue, string AddOrDeduck, out string xmlstr)
        {
            xmlstr = string.Empty;
            string selectSql = string.Empty;
            if (AddOrDeduck.Equals("deduck"))
            {
                selectSql = "exec SP_deduckSumByDepartmentNew @AreaCode,@SDate,@EDate";
            }
            else
            {
                selectSql = "exec SP_scoreAddSumByDepartmentNew @AreaCode,@SDate,@EDate";
            }

            try
            {
                SqlParameter param0 = new SqlParameter("@AreaCode", AreaCode);
                SqlParameter param1 = new SqlParameter("@SDate", "2009-11-01");
                SqlParameter param2 = new SqlParameter("@EDate", DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd"));

                if (StartDate.Year >= 2009)
                {
                    param1 = new SqlParameter("@SDate", StartDate.ToString("yyyy-MM-dd"));
                }

                if (EndDate > DateTime.Now.AddYears(-5))
                {
                    param2 = new SqlParameter("@EDate", EndDate.ToString("yyyy-MM-dd"));
                }

                SqlParameter[] parameters = { param0, param1, param2 };
                var list = dbContext.Database.SqlQuery<ScoreAddSumByDepartmentNew>(selectSql, parameters).ToList();

                if (list != null && list.Count > 0)
                {
                    IList<ScoreAddSumByDepartmentNew> listsource = list;

                    if (StartDate.Year == 2009)
                    {
                        listsource = processZFC(list);
                    }
                    else if (StartDate.Year < 2009) //2009年以前的统计结果都静态的存在了a_KhUserDefine表里
                    {
                        listsource = processData(list);
                    }
                    CreateXML(listsource, CorpTypeValue, out xmlstr);
                    return list;
                }

                return list;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public void CreateXML(IList<ScoreAddSumByDepartmentNew> list, string corptype, out string xmlstr)
        {
            DataTable dt = IListOut(list);

            StringBuilder sb = new StringBuilder();
            sb.Append("<chart showValues='1' canvasBgColor='D9E5F1' canvasBaseColor='D9E5F1' formatNumberScale='0' baseFont='宋体' baseFontSize='12'>");

            //横坐标
            sb.Append("<categories>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("<category label='" + dt.Rows[i]["部门"].ToString() + "' />");
            }
            sb.Append("</categories>");

            //柱形图
            sb.Append("<dataset seriesName='加分记录数' >");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                sb.Append("<set value='" + dt.Rows[j]["" + corptype + ""].ToString() + "' />");
            }
            sb.Append("</dataset>");

            //走势图
            sb.Append("<dataset seriesname='加分分数' renderAs='Line' color='BBDA00' anchorSides='4' anchorRadius='5' anchorBgColor='BBDA00' anchorBorderColor='FFFFFF' anchorBorderThickness='2'>");
            for (int k = 0; k < dt.Rows.Count; k++)
            {
                sb.Append("<set value='" + dt.Rows[k]["" + corptype + "s"].ToString() + "' />");
            }
            sb.Append("</dataset>");
            sb.Append("<styles>");
            sb.Append("<definition><style type='font' name='CaptionFont' size='18' color='666666' /><style type='font' name='SubCaptionFont' bold='0' /></definition>");
            sb.Append("<application><apply toObject='caption' styles='CaptionFont' /><apply toObject='SubCaption' styles='SubCaptionFont' /></application>");
            sb.Append("</styles>");
            sb.Append("</chart>");

            xmlstr = sb.ToString();
        }

        /// <summary>
        /// 将IList转化为datatable
        /// </summary>
        /// <param name="ResList"></param>
        /// <returns></returns>
        public DataTable IListOut(IList<ScoreAddSumByDepartmentNew> ResList)
        {
            DataTable TempDT = new DataTable();

            //此处遍历IList的结构并建立同样的DataTable
            System.Reflection.PropertyInfo[] p = ResList[0].GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo pi in p)
            {
                TempDT.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
            }

            for (int i = 0; i < ResList.Count; i++)
            {
                IList TempList = new ArrayList();
                //将IList中的一条记录写入ArrayList
                foreach (System.Reflection.PropertyInfo pi in p)
                {
                    object oo = pi.GetValue(ResList[i], null);
                    TempList.Add(oo);
                }

                object[] itm = new object[p.Length];
                //遍历ArrayList向object[]里放数据
                for (int j = 0; j < TempList.Count; j++)
                {
                    itm.SetValue(TempList[j], j);
                }
                //将object[]的内容放入DataTable
                TempDT.LoadDataRow(itm, true);
            }
            //返回DataTable
            return TempDT;
        }

        //青岛JG逻辑：2009年的统计结果
        public IList<ScoreAddSumByDepartmentNew> processZFC(IList<ScoreAddSumByDepartmentNew> list)
        {
            foreach (var item in list)
            {
                int deptID = int.Parse(item.deptid.ToString());
                if (deptID == 4)//执法处
                {
                    //item.施工企业 = int.Parse(item.施工企业.ToString()) + 17;
                    //item.施工企业s = int.Parse(item.施工企业s.ToString()) + 51;
                    //item.监理企业 = int.Parse(item.监理企业.ToString()) + 9;
                    //item.监理企业s = int.Parse(item.监理企业s.ToString()) + 54;
                    item.施工企业 = int.Parse(item.施工企业.ToString()) + 17;
                    item.施工企业s = int.Parse(item.施工企业s.ToString()) + 51;
                    item.监理企业 = int.Parse(item.监理企业.ToString()) + 9;
                    item.监理企业s = int.Parse(item.监理企业s.ToString()) + 54;
                }
            }

            foreach (var item in list)
            {
                int deptID = int.Parse(item.deptid.ToString());
                if (deptID == 3)//建管处
                {

                    item.施工企业 = int.Parse(item.施工企业.ToString()) + 280;
                    item.施工企业s = int.Parse(item.施工企业s.ToString()) + 1047;
                    item.监理企业 = int.Parse(item.监理企业.ToString()) + 25 + 18;
                    item.监理企业s = int.Parse(item.监理企业s.ToString()) + 99 + 108;
                    item.项目经理 = int.Parse(item.项目经理.ToString()) + 92;
                    item.项目经理s = int.Parse(item.项目经理s.ToString()) + 811;
                    item.项目总监 = int.Parse(item.项目总监.ToString()) + 18;
                    item.项目总监s = int.Parse(item.项目总监s.ToString()) + 108;
                }
            }
            return list;
        }

        //青岛JG逻辑：2009年以前的统计结果都静态的存在了a_KhUserDefine表里
        public IList<ScoreAddSumByDepartmentNew> processData(IList<ScoreAddSumByDepartmentNew> list)
        {
            try
            {
                foreach (var item in list)
                {
                    int deptID = int.Parse(item.deptid.ToString());
                    a_KhUserDefine userdefine = dbContext.UserDefine.Where(u => u.treeID == 2001 && u.deptID == deptID).FirstOrDefault();

                    if (userdefine != null)
                    {
                        int sum = 0;
                        item.施工企业 = int.Parse(item.施工企业.ToString()) + int.Parse(userdefine.Count_sg.ToString());
                        item.监理企业 = int.Parse(item.监理企业.ToString()) + int.Parse(userdefine.Count_jl.ToString());

                        item.施工企业s = int.Parse(item.施工企业s.ToString()) + int.Parse(userdefine.Score_sg.ToString());
                        item.监理企业s = int.Parse(item.监理企业s.ToString()) + int.Parse(userdefine.Score_jl.ToString());

                        item.建设单位 = int.Parse(item.建设单位.ToString()) + int.Parse(userdefine.Count_jsdw.ToString());
                        item.建设单位s = int.Parse(item.建设单位s.ToString()) + int.Parse(userdefine.Score_jsdw.ToString());
                        item.招标代理机构 = int.Parse(item.招标代理机构.ToString()) + int.Parse(userdefine.Count_zbdl.ToString());
                        item.招标代理机构s = int.Parse(item.招标代理机构s.ToString()) + int.Parse(userdefine.Score_zbdl.ToString());
                        item.造价咨询企业 = int.Parse(item.造价咨询企业.ToString()) + int.Parse(userdefine.Count_zjzx.ToString());
                        item.造价咨询企业s = int.Parse(item.造价咨询企业s.ToString()) + int.Parse(userdefine.Score_zjzx.ToString());
                    }
                }
            }
            catch (System.Exception e)
            {

            }
            return list;
        }

        public IList<SumVer> GetSumVerByCorp(string AreaCode, DateTime SDate, DateTime EDate)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@SDate",SDate),
                new SqlParameter("@EDate",EDate)
            };
            var list = dbContext.Database.SqlQuery<SumVer>("EXEC SP_SumVerByCorp @AreaCode,@SDate,@EDate", parameters).ToList();
            return list;
        }

        public IList<SumVer> GetSumVerByPerson(string AreaCode, DateTime SDate, DateTime EDate)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@SDate",SDate),
                new SqlParameter("@EDate",EDate)
            };
            var list = dbContext.Database.SqlQuery<SumVer>("EXEC SP_SumVerByPerson @AreaCode,@SDate,@EDate", parameters).ToList();
            return list;
        }

        public IList<DeduckSumByDepartmentNew> GetDataDeduck(string AreaCode, DateTime StartDate, DateTime EndDate, string CorpTypeValue, out string xmlstr)
        {
            xmlstr = string.Empty;
            string selectSql = string.Empty;
            selectSql = "exec SP_deduckSumByDepartmentNew @AreaCode,@SDate,@EDate";
            DataSet ds = new DataSet();

            try
            {
                SqlParameter param0 = new SqlParameter("@AreaCode", AreaCode);
                SqlParameter param1 = new SqlParameter("@SDate", "2009-11-01");
                SqlParameter param2 = new SqlParameter("@EDate", DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd"));

                if (StartDate.Year >= 2009)
                {
                    param1 = new SqlParameter("@SDate", StartDate.ToString("yyyy-MM-dd"));
                }

                if (EndDate > DateTime.Now.AddYears(-5))
                {
                    param2 = new SqlParameter("@EDate", EndDate.ToString("yyyy-MM-dd"));
                }

                SqlParameter[] parameters = { param0, param1, param2 };

                var list = dbContext.Database.SqlQuery<DeduckSumByDepartmentNew>(selectSql, parameters).ToList();

                if (list != null)
                {
                    IList<DeduckSumByDepartmentNew> listsource = list;
                    CreateXMLDeduck(listsource, CorpTypeValue, out xmlstr);
                    return list;
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public void CreateXMLDeduck(IList<DeduckSumByDepartmentNew> list, string corptype, out string xmlstr)
        {
            DataTable dt = IListOutDeduck(list);

            StringBuilder sb = new StringBuilder();
            sb.Append("<chart showValues='1' showColumnShadow='0'  formatNumberScale='0' baseFont='宋体' baseFontSize='12'>");

            //横坐标
            sb.Append("<categories>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("<category label='" + dt.Rows[i]["部门"].ToString() + "' />");
            }
            sb.Append("</categories>");
            //走势图
            sb.Append("<dataset seriesname='扣分分数' parentYAxis='S' renderAs='Line'  anchorSides='4' anchorRadius='5' anchorBgColor='AFD8F8' anchorBorderColor='FFFFFF' anchorBorderThickness='2'>");
            for (int k = 0; k < dt.Rows.Count; k++)
            {
                sb.Append("<set value='" + dt.Rows[k]["" + corptype + "s"].ToString() + "' />");
            }
            sb.Append("</dataset>");
            sb.Append("<styles>");
            sb.Append("<definition><style type='font' name='CaptionFont' size='18' color='666666' /><style type='font' name='SubCaptionFont' bold='0' /></definition>");
            sb.Append("<application><apply toObject='caption' styles='CaptionFont' /><apply toObject='SubCaption' styles='SubCaptionFont' /></application>");
            sb.Append("</styles>");

            //柱形图
            sb.Append("<dataset seriesName='扣分记录数' >");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                sb.Append("<set value='" + dt.Rows[j]["" + corptype + ""].ToString() + "' />");
            }
            sb.Append("</dataset>");


            sb.Append("</chart>");

            xmlstr = sb.ToString();
        }

        public DataTable IListOutDeduck(IList<DeduckSumByDepartmentNew> ResList)
        {
            DataTable TempDT = new DataTable();

            //此处遍历IList的结构并建立同样的DataTable
            System.Reflection.PropertyInfo[] p = ResList[0].GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo pi in p)
            {
                TempDT.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
            }

            for (int i = 0; i < ResList.Count; i++)
            {
                IList TempList = new ArrayList();
                //将IList中的一条记录写入ArrayList
                foreach (System.Reflection.PropertyInfo pi in p)
                {
                    object oo = pi.GetValue(ResList[i], null);
                    TempList.Add(oo);
                }

                object[] itm = new object[p.Length];
                //遍历ArrayList向object[]里放数据
                for (int j = 0; j < TempList.Count; j++)
                {
                    itm.SetValue(TempList[j], j);
                }
                //将object[]的内容放入DataTable
                TempDT.LoadDataRow(itm, true);
            }
            //返回DataTable
            return TempDT;
        }


        public IList<SGandJLInfo> GetSGandJLInfo(string Code)
        {
            string sqlStr = string.Format(@"
                SELECT
                    bzk.ID,bzk.Code,hpc.CorpType,hpc.CorpName,tq.DetailName, 
                    tq.QualityCode, kc.SafeLicenceNo,kc.LicenceDept ,
                    '' as Contract, '' as BeiAnJiGuan, hpm.ManagerName,
                    hpm.ManagerCode,tm.Tel,tm.ID as EMID,hpc.CorpUserGuid
                FROM h_projectBzk_New as bzk
                LEFT JOIN h_ProjectCorp as hpc on bzk.ID = hpc.ProjectId
                LEFT JOIN tb_Corp_Quality as tq on hpc.CorpUserGuid  = tq.CorpUserGuid AND tq.IsMain = 1
                LEFT JOIN kh_corptable as kc ON hpc.CorpUserGuid  = kc.CorpUserGuid
                LEFT JOIN h_ProjectManager as hpm ON hpm.ProjectCorpId  = hpc.Id
                LEFT JOIN tb_emp_manager as tm ON tm.ID = hpm.ManagerId 
                WHERE bzk.bKind = 2 AND  (hpc.CorpType2 = 0 or hpc.CorpType2 is null) and hpc.CorpType in (15,42)
                AND hpm.ManagerType IN (0,2) AND bzk.IsDel = 0 AND hpc.IsDel = 0 AND hpm.IsDel = 0 
                AND bzk.Code like '{0}%'", Code);

            var list = dbContext.Database.SqlQuery<SGandJLInfo>(sqlStr).ToList();
            return list;
        }

        public IList<h_ProjectCorp> GetSGProjectList(int CorpType, int ProjectID)
        {
            string sqlStr = string.Format(@"SELECT
                            hpc.*
                        FROM h_ProjectCorp as hpc
                        LEFT JOIN tb_Corp_Quality as tq on hpc.CorpUserGuid = tq.CorpUserGuid AND tq.IsMain = 1
                        LEFT JOIN kh_corptable as kc ON hpc.CorpUserGuid = kc.CorpUserGuid
                        LEFT JOIN h_ProjectManager as hpm ON hpm.ProjectId = hpc.ProjectId AND hpm.ProjectCorpId = hpc.Id
                        WHERE hpc.CorpType = 15
                        AND hpc.CorpType2 = {0}
                        AND hpm.ManagerType = 0
                        AND hpc.ProjectId = {1}", CorpType, ProjectID);

            var list = dbContext.Database.SqlQuery<h_ProjectCorp>(sqlStr).ToList();
            return list;
        }

        public IList<VerProject> GetVerProjectList(string CorpName, string ProjectName, string ManagerName,
            int ProjectType, string AreaCode, DateTime BeginDate, int VisualProgress, int Dogid, string CorpUserGuid,
            int bStatus, int pageIndex, int pageSize, out int dataCount)
        {
            SqlParameter[] param = { 
                new SqlParameter("@CorpName",CorpName),
                new SqlParameter("@ProjectName",ProjectName),
                new SqlParameter("@ManagerName",ManagerName),
                new SqlParameter("@ProjectType",ProjectType),
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@BeginDate",BeginDate.ToString("yyyy-MM-dd")),
                new SqlParameter("@VisualProgress",VisualProgress),
                new SqlParameter("@DogID",Dogid),
                new SqlParameter("@CorpUserGuid",CorpUserGuid),
                new SqlParameter("@bStatus",bStatus)
                
            };
            string sqlStr = @"EXEC sp_h_projectbzk_new_GetProjectInfoListTemp @CorpName,@ProjectName,@ManagerName,
                @ProjectType,@AreaCode,@BeginDate,@VisualProgress,@DogID,@CorpUserGuid,@bStatus";
            var query = dbContext.Database.SqlQuery<VerProject>(sqlStr, param);
            List<VerProject> list = query.ToList();
            dataCount = list.Count;

            return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<int> GetDeduckList(string ProjectName, string CorpUserGuid)
        {
            string sqlStr = string.Format(@"
                SELECT SUM(i.Score) AS koufen 
                FROM dbo.e_Deduck d JOIN dbo.e_DeduckItem i  
                ON d.ID=i.DeduckID 
                WHERE d.ProjectName='{0}' 
                and   d.CorpUserGuid='{1}'  
                GROUP BY d.ProjectName, d.CorpUserGuid", ProjectName, CorpUserGuid);

            var list = dbContext.Database.SqlQuery<int>(sqlStr).ToList();
            return list;
        }

        public IList<int> GetTotalCorpScoreList(string ProjectName, string CorpUserGuid, string DogID)
        {
            string sqlStr = string.Format(@"
                SELECT SUM(Total_CorpScore) AS  TotalCorpScore
                FROM dbo.e_ProjectPrizePunish
                WHERE projectName='{0}' and CorpUserGuid='{1}'
                GROUP BY ProjectName  
                UNION
                SELECT SUM(Total_CorpScore) AS TotalCorpScore
                FROM dbo.e_PrizeBZH
                WHERE projectName= '{0}' and CorpDogID='{2}'
                GROUP BY ProjectName", ProjectName, CorpUserGuid, DogID);
            var list = dbContext.Database.SqlQuery<int>(sqlStr).ToList();
            return list;
        }

        public string GetUserCorpIDByDog(string DogID)
        {
            int dogid = 0;
            int.TryParse(DogID, out dogid);
            var CorpID = (from us in dbContext.T_Sys_Users
                          where us.DogID == dogid
                          select us.CorpID).FirstOrDefault();
            return CorpID == null ? "" : CorpID.ToString();
        }

        public IList<StatisticsCorpInfo> GetStatisticsCorpList(string AreaCode, string projectName, string corpName, string AwardType,
            int DogID, string Date, int pageIndex, int pageSize, out int dataCount)
        {
            string sqlStr = @"
                SELECT
                    classid,ProjectName,ProjectID,CorpName,CorpDogID,CorpID,
                    CONVERT(VARCHAR,BuildAreas) AS Areas,CONVERT(VARCHAR,Cost) AS Cost ,ID,'P' AS SIGN,RegisterDate,
                    (SELECT SUM(Score) FROM e_Score WHERE ID=r.KHID1 OR ID=r.KHID2) Score,0 AS deScore 
                FROM e_ProjectPrizePunish r  WHERE r.AreaCode like '"+AreaCode+"%' "+
                @"UNION ALL
                SELECT
                    classid,ProjectName,ProjectID,CorpName,CorpDogID,CorpID,
                    CONVERT(VARCHAR,AuditArea) AS Areas,CONVERT(VARCHAR,AuditCost) AS Cost,ID,'O' AS SIGN,RegisterDate,
                    (PersonScore+CorpScore)Score,0 AS deScore
                FROM e_OuterPrize o  WHERE o.AreaCode like '"+AreaCode+"%' "+
                @"UNION ALL
                SELECT
                    (SELECT CONVERT(VARCHAR,classid) FROM  dbo.tb_Corp_CompanyInfo WHERE ID=p.CorpID)AS classid,
                    ProjectName,ProjectID,CorpName,CorpDogID,CorpID,'0' AS Areas,'0' AS cost,ID,'B' AS SIGN,RegisterDate,
                    (SELECT SUM(Score) FROM e_Score WHERE ID=p.KHID1 OR ID=p.KHID2) score,0 AS descroe
                FROM e_PrizeBZH p WHERE p.IsrtAreaCode like '"+AreaCode+"%' "+
                @"UNION ALL
                SELECT
                     d.classid, ProjectName,ProjectID,(SELECT tc.CorpName FROM  tb_Corp_CompanyInfo tc WHERE ID=d.CorpID)AS CorpName,
                     CorpDogID,d.CorpID,'0' AS Areas,'0' AS cost,d.ID,'D' AS SIGN,d.RegisterDate,0 AS Score,
                    (SELECT SUM(Score) FROM e_DeduckItem WHERE DeduckID=d.ID ) AS deScore 
                FROM e_Deduck d LEFT JOIN T_Sys_Users u ON u.UserGuid = d.CorpUserGuid WHERE u.AreaCode like '" + AreaCode+"%' "+
                "ORDER BY ProjectName DESC";

            if (projectName != "" || corpName != "" || AwardType != "" || DogID > 0)
            {
                int? CorpID = 0;
                if (DogID > 0)
                {
                    CorpID = (from us in dbContext.T_Sys_Users
                              where us.DogID == DogID
                              select us.CorpID).FirstOrDefault();
                    CorpID = CorpID ?? 0;
                }
                sqlStr = @"select * from (" + sqlStr.Substring(0, sqlStr.IndexOf("ORDER")) + ") tb where 1=1 ";
                if (!string.IsNullOrEmpty(projectName))
                {
                    sqlStr += " and ProjectName like '%" + projectName + "%'";
                }
                if (!string.IsNullOrEmpty(corpName))
                {
                    sqlStr += " and CorpName like '%" + corpName + "%'";
                }
                if (!string.IsNullOrEmpty(AwardType))
                {
                    sqlStr += " and SIGN='" + AwardType.ToLower() + "'";
                }
                if (CorpID != 0)
                {
                    sqlStr += " and CorpID=" + CorpID;
                }

                sqlStr += " and convert(varchar(50),RegisterDate,120) like'" + Date + "%'  ORDER BY ProjectName DESC";
            }

            var query = dbContext.Database.SqlQuery<StatisticsCorpInfo>(sqlStr);
            try
            {
                IList<StatisticsCorpInfo> list = query.ToList();
                dataCount = list.Count;
                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            catch (Exception ex)
            {
                dataCount = 0;
                return new List<StatisticsCorpInfo>() { 
                    new StatisticsCorpInfo(){
                        classid="11111"
                        ,ProjectName="综合楼"
                        ,ProjectID=-1
                        ,CorpName=ex.Message
                        ,CorpDogID=126547
                        ,CorpID=421
                        ,Areas="0"
                        ,Cost="115.62"
                        ,ID=11775
                        ,SIGN="O"
                        ,RegisterDate= DateTime.Now
                        ,Score=null
                        ,deScore=null
                    }
                };
            }
        }

        public IList<AddScoreClass> GetAddScoreClass(string AreaCode, DateTime SDate, DateTime EDate, string Type, string DeptName)
        {
            SqlParameter[] parameter ={
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@SDate",SDate),
                new SqlParameter("@EDate",EDate),
                new SqlParameter("@Role",Type),
                new SqlParameter("@DeptName",DeptName)
            };

            try
            {
                var list = dbContext.Database.SqlQuery<AddScoreClass>("exec SP_scoreAddSumByClass @AreaCode,@SDate,@EDate,@Role,@DeptName", parameter).ToList();
                return list;
            }
            catch (Exception)
            {
                return null;
            }

        }

        public string GetTranTypeTitle(string AreaCode, string RoleID)
        {
            string sql = "select roleName as title from e_SckhVerSumClass where roleid=@RoleID and sysAreaCode like '@AreaCode%'";

            SqlParameter[] parameter ={
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@RoleID",RoleID)
            };

            var list = dbContext.Database.SqlQuery<string>(sql, parameter).ToList();
            string result = "";
            if (list.Count > 0)
            {
                result = list[0];
            }
            return result;
        }

        public IList<DeduckSumByClass> GetHorDeduckSumByClassCorp(string AreaCode, DateTime StartDate, DateTime EndDate, string TypeValue, int Role, string DeptName, out string xmlstr)
        {
            xmlstr = string.Empty;
            string selectSql = "exec SP_deduckSumByClass @AreaCode,@SDate,@EDate,@Role,@DeptName";

            try
            {
                SqlParameter[] parameters ={
                new SqlParameter("@AreaCode", AreaCode),
                new SqlParameter("@SDate",StartDate),
                new SqlParameter("@EDate",EndDate),
                new SqlParameter("@Role",Role),
                new SqlParameter("@DeptName",DeptName)
                };

                var list = dbContext.Database.SqlQuery<DeduckSumByClass>(selectSql, parameters).ToList();

                if (list != null)
                {
                    IList<DeduckSumByClass> listsource = list;

                    CreateXMLHorDeduckSumByClassCorp(listsource, TypeValue, out xmlstr);
                    return list;
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public void CreateXMLHorDeduckSumByClassCorp(IList<DeduckSumByClass> list, string type, out string xmlstr)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dt = IListOutCommon(list as IList);
            sb.Append("<chart showPercentageInLabel='1' showValues='1' showLabels='0' showLegend='1' baseFont='宋体' baseFontSize='12'>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("<set displayValue='" + dt.Rows[i]["" + type + ""].ToString() + "' value='" + dt.Rows[i]["" + type + ""].ToString() + "' label='" + dt.Rows[i]["AddClassName"].ToString() + "'/>");
            }
            sb.Append("</chart>");

            xmlstr = sb.ToString();
        }

        public DataTable IListOutCommon(IList ResList)
        {
            DataTable TempDT = new DataTable();

            //此处遍历IList的结构并建立同样的DataTable
            System.Reflection.PropertyInfo[] p = ResList[0].GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo pi in p)
            {
                TempDT.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
            }

            for (int i = 0; i < ResList.Count; i++)
            {
                IList TempList = new ArrayList();
                //将IList中的一条记录写入ArrayList
                foreach (System.Reflection.PropertyInfo pi in p)
                {
                    object oo = pi.GetValue(ResList[i], null);
                    TempList.Add(oo);
                }

                object[] itm = new object[p.Length];
                //遍历ArrayList向object[]里放数据
                for (int j = 0; j < TempList.Count; j++)
                {
                    itm.SetValue(TempList[j], j);
                }
                //将object[]的内容放入DataTable
                TempDT.LoadDataRow(itm, true);
            }
            //返回DataTable
            return TempDT;
        }

        public IList<DecAddComparisonByQM_ZBDLRY> GetDecAddComparisonByQM(string AreaCode, int SumYear, int SumYear2,
                                                                       int SumFlag, int SumQM, int SumQM2,
                                                                       int Role, string DeptName)
        {

            SqlParameter[] parameter ={
            new SqlParameter("@SumYear",SumYear),
            new SqlParameter("@SumQM",SumQM),
            new SqlParameter("@SumYear2",SumYear2),
            new SqlParameter("@SumQM2",SumQM2),
            new SqlParameter("@SumFlag",SumFlag),
            new SqlParameter("@Role",Role),
            new SqlParameter("@DeptName",DeptName),
            new SqlParameter("@AreaCode",AreaCode)
                                      };
            var list = dbContext.Database.SqlQuery<DecAddComparisonByQM_ZBDLRY>("EXEC SP_DecAddComparisonByQM @SumYear,@SumQM,@SumYear2,@SumQM2,@SumFlag,@Role,@DeptName,@AreaCode", parameter).ToList();


            return list;
        }


        public IList<HorDeduckClassListModel> GetHorDeduckClassList(string AreaCode, DateTime StartDate, DateTime EndDate,
                                          string CorpDogID, string BFlag, int id, string Type, string DeptName, out int listCount)
        {
            //re_DepartmentUsers 替换 a_KhUserDefine
            string sql = "select b.deptid,b.DepartName as deptname,c.id AS itemid,a.ID,a.classid,a.CorpDogID,a.bFlag,a.ProjectName,c.LawActive,a.Name,c.Score,a.AuthorName,a.ExePerson,a.SignDate"
                        + " from e_Deduck a join re_DepartmentUsers b on a.AuthorID =b.dogid join e_DeduckItem c on a.id=c.DeduckID"
                        + " where  bFlowStatus like '1%' and SignDate>='" + StartDate + "' and SignDate<='" + EndDate + "' ";

            if (CorpDogID != "-1")
            {
                sql += " and CorpDogID =" + CorpDogID;
            }
            if (BFlag != "0")
            {
                sql += " and PrizeFlag=" + BFlag;
            }
            else
            {
                sql += " and (PrizeFlag=0 or PrizeFlag is null)";
            }

            if (id > 0)
            {
                sql += " and c.KHID=" + id;
            }
            else
            {
                if (Type != "")
                {
                    sql += " and a.bflag=" + Type;
                }
                if (DeptName != "")
                {
                    sql += " and b.DepartName='" + DeptName + "'";
                }
            }
            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " AND b.areacode like '" + AreaCode + "%'";
            }
            sql += " order by a.SignDate desc";

            var query = dbContext.Database.SqlQuery<HorDeduckClassListModel>(sql);

            try
            {
                IList<HorDeduckClassListModel> list = query.ToList();
                listCount = list.Count();
                return list;

            }
            catch (Exception)
            {
                listCount = 0;
                return null;
            }

        }


        public IList<HorAddClassListModel> GetHorAddClassList(string AreaCode, DateTime StartDate, DateTime EndDate,
                                          string CorpDogID, string BFlag, string Type, string DeptName, string SelectedValue, out int listCount)
        {
            string sql = "";
            string condition = " and AuditDate>='" + StartDate + "' and AuditDate<='" + EndDate + "'";

            switch (Type)
            {
                case "1"://项目经理
                    condition += "  and roleid in (15,43,17) and PersonScore>0 ";//PersonID >0";  //注意 劳务分包企业有项目经理
                    break;
                case "2"://项目总监
                    condition += "  and roleid =42 and PersonScore>0 ";//PersonID >0";
                    break;
                case "3":
                    condition += "  and roleid =46 and PersonScore>0 ";//造价
                    break;
                case "15"://施工企业
                    condition += "  and roleid in (15,43,17) and CorpClassID<>2507 and corpScore>0 ";
                    break;
                case "43"://劳务分包企业
                    condition += "  and roleid in (15,43,17) and CorpClassID=2507 and corpScore>0 ";
                    break;
                default:
                    condition += " and roleid = " + Type;// +" and CorpScore>0 ";
                    break;
            }
            if (DeptName != "")
            {
                condition += " and DepartName like '%" + DeptName + "%'";
            }
            if (CorpDogID != "-1")
            {
                condition += " and CorpDogID =" + CorpDogID;
            }

            if (BFlag != "9")
            {
                if (SelectedValue == "工程类获奖")
                {
                    condition += " and type='e_ProjectPrizePunish'";
                }
                else if (SelectedValue == "非工程类获奖")
                {
                    condition += " and type='e_PrizePunish'";
                }
            }
            if (!string.IsNullOrEmpty(AreaCode))
            {
                condition += " AND b.areacode like '" + AreaCode + "%'";
            }

            if (BFlag != "0")//无 分类
            {
                ////re_DepartmentUsers 替换 kh_ManageDeptList
                sql = @"select 
                        a.ywid as ID, 
                        case a.ClassID when 2530 then 'e_OuterPrize' 
                        when 2540 then 'e_PrizePunish' 
                        when 2550 then 'e_ProjectPrizePunish' 
                        when 2570 then 'e_PrizeBZH' 
                        else convert(varchar(10),a.ClassID) 
                        end as Type ,
                        a.title,a.projectname,a.personName,
                        a.corpdogid,a.CorpName,a.CorpScore,
                        a.PersonScore,a.AuditDogID,a.DeptName
                        from tb_eKHData a join re_DepartmentUsers b on a.AuditDogID=b.DogID where subClassID=@Flag  " + condition + " ORDER BY AuditDate DESC";
                SqlParameter[] parameter = { new SqlParameter("@Flag", BFlag) };
                var query = dbContext.Database.SqlQuery<HorAddClassListModel>(sql, parameter);

                IList<HorAddClassListModel> list = query.ToList();
                listCount = list.Count();
                return list;
            }
            else
            {
                ////re_DepartmentUsers 替换 kh_ManageDeptList
                sql = @"select 
                        a.ywid as ID, 
                        case a.ClassID when 2530 then 'e_OuterPrize' 
                        when 2540 then 'e_PrizePunish' 
                        when 2550 then 'e_ProjectPrizePunish' 
                        when 2570 then 'e_PrizeBZH' 
                        else convert(varchar(10),a.ClassID) 
                        end as Type ,
                        a.title,a.projectname,a.personName,
                        a.corpdogid,a.CorpName,a.CorpScore,
                        a.PersonScore,a.AuditDogID,a.DeptName
                        from tb_eKHData a join re_DepartmentUsers b on a.AuditDogID=b.DogID where (subClassID=0 or subClassID is null) " + condition + " ORDER BY AuditDate DESC";
                var query = dbContext.Database.SqlQuery<HorAddClassListModel>(sql);

                IList<HorAddClassListModel> list = query.ToList();
                listCount = list.Count();
                return list;
            }

        }


        public IList<DecDeduckComparisonByQM_ZBDLRY> GetDecDeduckComparisonByQM(string AreaCode, int SumYear, int SumYear2,
                                                                       int SumFlag, int SumQM, int SumQM2,
                                                                       int Role, string DeptName)
        {

            SqlParameter[] parameter ={
            new SqlParameter("@SumYear",SumYear),
            new SqlParameter("@SumQM",SumQM),
            new SqlParameter("@SumYear2",SumYear2),
            new SqlParameter("@SumQM2",SumQM2),
            new SqlParameter("@SumFlag",SumFlag),
            new SqlParameter("@Role",Role),
            new SqlParameter("@DeptName",DeptName),
            new SqlParameter("@AreaCode",AreaCode)
                                      };
            var list = dbContext.Database.SqlQuery<DecDeduckComparisonByQM_ZBDLRY>("EXEC SP_DecDeduckComparisonByQM @SumYear,@SumQM,@SumYear2,@SumQM2,@SumFlag,@Role,@DeptName,@AreaCode", parameter).ToList();


            return list;
        }

        public IList<ScoreAddSumByDepartmentNew> ScoreAddSumByDepartmentNew(string AreaCode, string SDate, string EDate)
        {
            SqlParameter[] parameter ={
            new SqlParameter("@AreaCode",AreaCode),
            new SqlParameter("@SDate",SDate),
            new SqlParameter("@EDate",EDate)
                                      };
            var list = dbContext.Database.SqlQuery<ScoreAddSumByDepartmentNew>("EXEC SP_scoreAddSumByDepartmentNew @AreaCode,@SDate,@EDate", parameter).ToList();
            return list;
        }

        public IList<DeduckSumByDepartmentNew> DeduckSumByDepartmentNew(string AreaCode, string SDate, string EDate)
        {
            SqlParameter[] parameter ={
            new SqlParameter("@AreaCode",AreaCode),
            new SqlParameter("@SDate",SDate),
            new SqlParameter("@EDate",EDate)
                                      };
            var list = dbContext.Database.SqlQuery<DeduckSumByDepartmentNew>("EXEC SP_deduckSumByDepartmentNew @AreaCode,@SDate,@EDate", parameter).ToList();
            return list;
        }

        public IList<VerCorpListModel> GetVerCorpList(string AreaCode, int Role, DateTime StartDate, DateTime EndDate, string QueryStr, int pageIndex, int pageSize, out int dataCount)
        {
            string insertStr = string.Empty;
            string WhereAdd = "";
            string WhereDeduck = "";

            if (Role != -1)
            {
                switch (Role)
                {
                    case 15:
                        WhereAdd += " and roleid in (15,43,17) and  CorpClassID <> 2507";
                        WhereDeduck += " and corpzzflag=" + Role;
                        insertStr += " and  A.bGrade in (15,43,17) and  A.classid <> 2507";
                        break;
                    case 43://劳务分包
                        WhereAdd += " and roleid in (15,43,17) and  CorpClassID = 2507";
                        WhereDeduck += " and corpzzflag=" + Role;
                        insertStr += " and  A.bGrade in (15,43,17) and  A.classid = 2507";
                        break;
                    default:
                        WhereAdd += " and roleid=" + Role;
                        WhereDeduck += " and corpzzflag=" + Role;
                        insertStr += " and  A.bGrade=" + Role;
                        break;
                }
            }
            if (Role == 15 || Role == 42)
            {
                insertStr += " and A.tmpID>0 ";
            }

            string sql = "select A.*,isnull(F.AddCount,0) as AddCount,isnull(F.AddScore,0) as AddScore,isnull(G.DeduckCount,0) as DeduckCount,isnull(G.DeduckScore,0) as DeduckScore "
            + " from V_e_ScoreKHResult A LEFT OUTER JOIN "
            + " (select corpdogid,count(*) as AddCount,Sum(CorpScore) as AddScore from tb_eKHData where AuditDate>='" + StartDate + "' and AuditDate<='" + EndDate + "' " + WhereAdd + " group by corpdogid) as F "
            + " on A.TreeID=F.corpdogid LEFT OUTER JOIN "
            + " (select corpdogid,count(*) as DeduckCount,Sum(Score) as DeduckScore from V_e_ScoreDeduck where SignDate>='" + StartDate + "' and SignDate<='" + EndDate + "' " + WhereDeduck + " and bFlowStatus like '1%' group by corpdogid) as G "
            + " on A.TreeID=G.corpdogid where A.treeid>0 and A.bMain=1";

            if (!QueryStr.Equals(""))
            {
                insertStr += " and  A.corpName like '%" + QueryStr + "%'";
            }
            sql += insertStr;
            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " AND a.areacode like '" + AreaCode + "%' ";
            }
            sql += " order by G.DeduckCount Desc,F.AddCount Desc";


            var query = dbContext.Database.SqlQuery<VerCorpListModel>(sql);
            try
            {
                IList<VerCorpListModel> list = query.ToList();
                dataCount = list.Count;
                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            catch (Exception ex)
            {
                dataCount = 0;
                return null;
            }

        }

        public IList<VerPersonListModel> GetVerPersonList(string AreaCode, int Role, int DogID, DateTime StartDate, DateTime EndDate,
                                                          string CorpName, string XmjlName, int pageIndex, int pageSize, out int dataCount)
        {
            string insertStr = string.Empty;
            string WhereAdd = "";
            string WhereDeduck = "";

            if (Role != -1)
            {
                switch (Role)
                {
                    case 1:
                        WhereAdd += " and roleid in (15,43,17) ";
                        WhereDeduck += " and corpzzflag=1";
                        insertStr += " and  A.bGrade in (15,43,17) and A.bFlag=1";
                        break;
                    case 15:
                        WhereAdd += " and roleid in (15,43,17) and  CorpClassID <> 2507";
                        WhereDeduck += " and corpzzflag=1";
                        insertStr += " and  A.bGrade in (15,43,17) and  A.classid <> 2507 and A.bFlag=1";
                        break;
                    case 43:
                        WhereAdd += " and roleid in (15,43,17) and  CorpClassID = 2507";
                        WhereDeduck += " and corpzzflag=1";
                        insertStr += " and A.bGrade in (15,43,17) and  A.classid = 2507 and A.bFlag=1";
                        break;
                    case 2:
                    case 42:
                        WhereAdd += " and roleid=42";
                        WhereDeduck += " and corpzzflag=2";
                        insertStr += " and  A.bGrade=42 and A.bFlag=2";
                        break;
                    case 3:
                    case 46:
                        WhereAdd += " and roleid=46";
                        WhereDeduck += " and corpzzflag=3";
                        insertStr += " and  A.bGrade=46 and A.bFlag=3";

                        break;
                }
            }

            string sql = "select A.*,isnull(F.AddCount,0) as AddCount,isnull(F.AddScore,0) as AddScore,isnull(G.DeduckCount,0) as DeduckCount,isnull(G.DeduckScore,0) as DeduckScore from V_e_ScoreKHResult A LEFT OUTER JOIN " +
           "(select corpdogid,personid,personName,personNo,count(*) as AddCount,Sum(PersonScore) as AddScore from tb_eKHData where AuditDate>='" + StartDate + "' and AuditDate<='" + EndDate + "' " + WhereAdd + " and personid>0 " +
           "group by corpdogid,personid,personName,personNo) as F on A.TreeID=F.corpdogid and A.PersonName=F.PersonName and A.KindlTitle=F.personNo LEFT OUTER JOIN " +
           "(select corpdogid,NameID,Name,LicenceCode,count(*) as DeduckCount,Sum(Score) as DeduckScore from V_e_ScoreDeduck where SignDate>='" + StartDate + "' and SignDate<='" + EndDate + "' " + WhereDeduck + " and bFlag in (1,2,3) " +
           "group by corpdogid,NameID,Name,LicenceCode ) as G on A.TreeID=G.corpdogid and A.PersonName=G.Name and A.KindlTitle=G.LicenceCode where A.treeid>0 and A.PersonName is not null";

            if (DogID != -1)
            {
                insertStr += " and A.TreeID='" + DogID + "' ";
            }

            string queryStr = CorpName;
            string queryStr2 = XmjlName;
            if (!queryStr.Equals(""))
            {
                insertStr += " and  A.corpName like '%" + queryStr + "%'";
            }
            if (!queryStr2.Equals(""))
            {
                insertStr += " and  A.PersonName like '%" + queryStr2 + "%'";
            }
            sql += insertStr;
            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " AND a.areacode like '" + AreaCode + "%' ";
            }
            sql += " order by G.DeduckCount Desc,F.AddCount Desc";

            var query = dbContext.Database.SqlQuery<VerPersonListModel>(sql);
            try
            {
                IList<VerPersonListModel> list = query.ToList();
                dataCount = list.Count;
                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            catch (Exception ex)
            {
                dataCount = 0;
                return null;
            }
        }


        public IList<ZDDecisionByQM_ZBDLRY> GetZDDecisionByQM(string AreaCode, int SumYear, int SumYear2,
                                                                int SumFlag, int SumQM, int SumQM2,
                                                                int Role, int IsAdd, int PrizeFlag)
        {

            SqlParameter[] parameter ={
            new SqlParameter("@SumYear",SumYear),
            new SqlParameter("@SumQM",SumQM),
            new SqlParameter("@SumYear2",SumYear2),
            new SqlParameter("@SumQM2",SumQM2),
            new SqlParameter("@SumFlag",SumFlag),
            new SqlParameter("@Role",Role),
            new SqlParameter("@IsAdd",IsAdd),
            new SqlParameter("@PrizeFlag",PrizeFlag),
            new SqlParameter("@AreaCode",AreaCode)
                                      };
            var list = dbContext.Database.SqlQuery<ZDDecisionByQM_ZBDLRY>("EXEC SP_ZDDecisionByQM @SumYear,@SumQM,@SumYear2,@SumQM2,@SumFlag,@Role,@IsAdd,@PrizeFlag,@AreaCode", parameter).ToList();

            return list;
        }


        public IList<ScoreZTBSumByDepartment_ZBDLRY> ScoreZTBSumByDepartment(string AreaCode, string SDate, string EDate)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@SDate",SDate),
                new SqlParameter("@EDate",EDate)
            };
            var list = dbContext.Database.SqlQuery<ScoreZTBSumByDepartment_ZBDLRY>("EXEC SP_ScoreZTBSumByDepartment @AreaCode,@SDate,@EDate", parameters).ToList();
            return list;
        }

        public IList<VerProjectAddMXNewModel> GetVerProjectAddMXNew(string AreaCode, int ID, out int listCount)
        {
            string sql = @" SELECT d.ID,i.ID AS ItemID,d.classid,d.AuthorName,d.Name,d.ProjectName,d.DeptName,i.LawActive,i.Score,t.CorpName FROM e_Deduck d 
  INNER JOIN e_DeduckItem i ON(d.ID=i.DeduckID) 
  INNER JOIN tb_Corp_CompanyInfo t ON(t.ID=d.CorpID)WHERE d.ID=" + ID;

            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " and t.AreaCode like '" + AreaCode + "%'";
            }
            
            var query = dbContext.Database.SqlQuery<VerProjectAddMXNewModel>(sql);
            try
            {
                IList<VerProjectAddMXNewModel> list = query.ToList();
                listCount = list.Count;
                return list;
            }
            catch (Exception ex)
            {
                listCount = 0;
                return null; ;
            }

        }

        public IList<AddScoreClass> ScoreAddSumByClassCorp(string AreaCode, string StartDate, string EndDate, string CorpDogID)
        {
            SqlParameter[] parameter ={
            new SqlParameter("@AreaCode",AreaCode),
            new SqlParameter("@SDate",StartDate),
            new SqlParameter("@EDate",EndDate),
            new SqlParameter("@CorpDogID",CorpDogID) 
            };

            string sql = @"exec SP_scoreAddSumByClass_Corp @AreaCode,@SDate,@EDate,@CorpDogID";
            var query = dbContext.Database.SqlQuery<AddScoreClass>(sql, parameter);

            IList<AddScoreClass> list = query.ToList();

            return list;
        }

        public IList<DeduckSumByClass> ScoreDeduckSumByClassCorp(string AreaCode, string StartDate, string EndDate, string CorpDogID)
        {
            SqlParameter[] parameter ={
            new SqlParameter("@AreaCode",AreaCode),                                        
            new SqlParameter("@SDate",StartDate),
            new SqlParameter("@EDate",EndDate),
            new SqlParameter("@CorpDogID",CorpDogID) 
            };

            string sql = @"exec SP_deduckSumByClass_Corp @AreaCode,@SDate,@EDate,@CorpDogID";
            var query = dbContext.Database.SqlQuery<DeduckSumByClass>(sql, parameter);

            IList<DeduckSumByClass> list = query.ToList();

            return list;
        }

        public IList<VerCorpListModel> GetScoreAddDeduckSumByCorp(string AreaCode, string StartDate, string EndDate, string CorpDogID)
        {
            
            string sql = "select A.*,isnull(F.AddCount,0) as AddCount,isnull(F.AddScore,0) as AddScore,isnull(G.DeduckCount,0) as DeduckCount,isnull(G.DeduckScore,0) as DeduckScore "
            + " from V_e_ScoreKHResult A LEFT OUTER JOIN "
            + " (select corpdogid,count(*) as AddCount,Sum(CorpScore) as AddScore from tb_eKHData where AuditDate>='" + StartDate + "' and AuditDate<='" + EndDate + "' group by corpdogid) as F "
            + " on A.TreeID=F.corpdogid LEFT OUTER JOIN "
            + " (select corpdogid,count(*) as DeduckCount,Sum(Score) as DeduckScore from V_e_ScoreDeduck where SignDate>='" + StartDate + "' and SignDate<='" + EndDate + "' and bFlowStatus like '1%' group by corpdogid) as G "
            + " on A.TreeID=G.corpdogid where A.treeid>0 and A.bMain=1 and A.TreeID=" + CorpDogID + " and A.AreaCode like '" + AreaCode + "%'";
            var query = dbContext.Database.SqlQuery<VerCorpListModel>(sql);

            IList<VerCorpListModel> list = query.ToList();

            return list;
        }


        public IList<SckhPublicManageCount_ZBDLRY> SckhPublicManageCountByScoreType(string AreaCode, int Type)
        {
            List<SckhPublicManageCount_ZBDLRY> list = new List<SckhPublicManageCount_ZBDLRY>();

            SqlParameter[] param = { 
                new SqlParameter("@AreaCode",AreaCode)
            };
            switch (Type)
            {
                case 1:
                    list = dbContext.Database.SqlQuery<SckhPublicManageCount_ZBDLRY>("EXEC sckhPublic_GetManageCount_AddScoreType @AreaCode", param).ToList();
                    break;
                case 2:
                    list = dbContext.Database.SqlQuery<SckhPublicManageCount_ZBDLRY>("EXEC sckhPublic_GetManageCount_ScoreType @AreaCode", param).ToList();
                    break;
            }
            return list;
        }

        public IList<ScoreMarkParagraphModel_ZBDLRY> ScoreMarkParagraph(string AreaCode, int bgrade, string tableName)
        {
            string sql = string.Empty;
            string wheres = GetWhere(bgrade.ToString());
            if (bgrade != 1 && bgrade != 2 && bgrade != 3)
            {
                if (bgrade == 43) bgrade = 11;
                sql = string.Format("SELECT (select count(*)  as a from " + tableName + "  t1 " +
                                        "LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID  " +
                                        "LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID  " +
                                        "WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=100 and bmain=1 and t1.corpname !='' and bflag={0}) as a," +
                                    "(select count(*) as b from e_SorcePublish_2006 t1  " +
                                        "LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID   " +
                                        "LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                        "WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=80 AND Year_Sorce<100 and t1.corpname !='' and bmain=1 and bflag={0}) as b," +
                                    "(select count(*) as c from e_SorcePublish_2006 t1   " +
                                      "  LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID  " +
                                        "LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID  " +
                                        "WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=60 AND Year_Sorce<80 and t1.corpname !='' and bmain=1 and bflag={0}) as c," +
                                    "(select count(*) as d from e_SorcePublish_2006 t1   " +
                                        "LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID " +
                                        "LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                       " WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce<60 and bmain=1 and t1.corpname !='' and bflag={0}) as d", bgrade, wheres);
            }
            else//项目经理、项目总监
            {
                sql = string.Format("SELECT (select count(*)  as a from " + tableName + "  t1  " +
                                     "   LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID   " +
                                        "LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                       "  WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=100 and personname!='' and bflag={0}) as a, " +
                                "     (select count(*) as b from e_SorcePublish_2006 t1   " +
                                      "   LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID   " +
                                     "    LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                       "  WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=80 and personname!='' AND Year_Sorce<100 and bflag={0}) as b, " +
                                   "  (select count(*) as c from e_SorcePublish_2006  t1   " +
                                       "  LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID   " +
                                       "  LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                     "    WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce>=60 and personname!='' AND Year_Sorce<80 and bflag={0}) as c, " +
                                  "   (select count(*) as d from e_SorcePublish_2006  t1   " +
                                        " LEFT JOIN T_SYS_USERS u ON t1.TREEID = u.DOGID   " +
                                       "  LEFT JOIN TB_CORP_COMPANYINFO c ON u.USERGUID = c.CORPUSERGUID   " +
                                        " WHERE (c.flowcode like '1%' or c.bcanbid like '%3%') {1} and Year_Sorce<60 and personname!='' and bflag={0}) as d", bgrade, wheres);
            }

            List<ScoreMarkParagraphModel_ZBDLRY> list = dbContext.Database.SqlQuery<ScoreMarkParagraphModel_ZBDLRY>(sql).ToList();
            return list;
        }

        private string GetWhere(string flag)
        {
            switch (flag)
            {
                case "1":
                case "11":
                    return " and u.RoleID in (15,17) and c.Classid <> '2507'";
                    break;
                case "43":
                    return " and u.RoleID=15 and c.Classid='2507'";
                    break;
                case "2":
                case "12":
                    return " and u.RoleID=42";
                    break;
                default:
                    return " and u.RoleID=" + flag;
                    break;
            }
        }

        public IList<VerProject> GetProjectInfoListByOwner(string Owner, string CorpName, string ProjectName, string ManagerName,
            int ProjectType, string AreaCode, string BeginDate, int VisualProgress, string CorpUserGuid,
            int bStatus, string CorpUserGuidJL, int SgID, int JlID, int pageIndex, int pageSize, out int dataCount)
        {
            SqlParameter[] param = { 
                new SqlParameter("@Owner",Owner),
                new SqlParameter("@CorpName",CorpName),
                new SqlParameter("@ProjectName",ProjectName),
                new SqlParameter("@ManagerName",ManagerName),
                new SqlParameter("@ProjectType",ProjectType),
                new SqlParameter("@AreaCode",AreaCode),
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@VisualProgress",VisualProgress),
                new SqlParameter("@CorpUserGuid",CorpUserGuid),
                new SqlParameter("@bStatus",bStatus),
                new SqlParameter("@CorpUserGuidJL",CorpUserGuidJL),
                new SqlParameter("@SgID",SgID),
                new SqlParameter("@JlID",JlID)
                
            };
            string sqlStr = @"EXEC sp_h_projectbzk_new_GetProjectInfoListByOwner @Owner,@CorpName,@ProjectName,@ManagerName,
                @ProjectType,@AreaCode,@BeginDate,@VisualProgress,@CorpUserGuid,@bStatus,@CorpUserGuidJL,@SgID,@JlID";
            var query = dbContext.Database.SqlQuery<VerProject>(sqlStr, param);
            List<VerProject> list = query.ToList();
            dataCount = list.Count;

            return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<DecZtbAddMXModel_ZBDLRY> DecZtbAddMX(string AreaCode, string DeptName, string SDate, string EDate)
        {
            ////re_DepartmentUsers 替换 kh_ManageDeptList
            string sql = "SELECT v_eKHDataZtbAdd.id,kh_ManageDeptList.DepartName,v_eKHDataZtbAdd.[name],kh_ManageDeptList.deptid,v_eKHDataZtbAdd.type,Title,projectname,auditDogid,"
                     + "kh_ManageDeptList.areaCode,v_eKHDataZtbAdd.AuditDogID as dogid,cast(v_eKHDataZtbAdd.Score_Bid as decimal(8,3)) as Score_Bid FROM re_DepartmentUsers as kh_ManageDeptList  "
                     + " JOIN (SELECT * FROM v_eKHDataZtbAdd WHERE Score_Bid>0 AND AuditDate>='" + SDate + "' AND AuditDate <='" + EDate + "') as v_eKHDataZtbAdd "
                     + " ON kh_ManageDeptList.DogID=v_eKHDataZtbAdd.AuditDogID where v_eKHDataZtbAdd.id is not null ";

            sql += " and areaCode like '" + AreaCode + "%'";
            if (DeptName != "-1")
            {
                sql += " and DepartName='" + DeptName + "'";

            }
            List<DecZtbAddMXModel_ZBDLRY> list = dbContext.Database.SqlQuery<DecZtbAddMXModel_ZBDLRY>(sql).ToList();
            return list;
        }

        public IList<ProjectDeduckMXModel_ZBDLRY> VerProjectDeduckMX(string AreaCode, string DeptName, string SDate, string EDate)
        {
//            string sql = @"select r.departname deptname,V_e_ScoreDeduck.ID,
//                         V_e_ScoreDeduck.AuthorName,V_e_ScoreDeduck.Name,
//                         V_e_ScoreDeduck.ProjectName,V_e_ScoreDeduck.ExePerson,
//                         V_e_ScoreDeduck.SignDate,0 as ItemID,V_e_ScoreDeduck.LawActive,V_e_ScoreDeduck.Score
//                         FROM re_DepartmentUsers r JOIN  
//                         (select a.*,b.Score_Bid from V_e_ScoreDeduck a join e_Score b on a.DeduckID=b.ID 
//                         where Score_Bid>0 AND a.SignDate>='" + SDate + "' AND a.SignDate<='" + EDate + "') "
//                        + "as V_e_ScoreDeduck  ON r.DogID=V_e_ScoreDeduck.AuthorID "
//                        + "where V_e_ScoreDeduck.id is not null ";

            string sql = @"select r.departname deptname,V_e_ScoreDeduck.ID,
                         V_e_ScoreDeduck.CorpDogID,V_e_ScoreDeduck.classid,
                         V_e_ScoreDeduck.bFlag,V_e_ScoreDeduck.diid  ItemID,
                         V_e_ScoreDeduck.AuthorName,V_e_ScoreDeduck.Name,
                         V_e_ScoreDeduck.ProjectName,V_e_ScoreDeduck.ExePerson,
                         V_e_ScoreDeduck.SignDate,V_e_ScoreDeduck.LawActive,V_e_ScoreDeduck.Score
                         FROM re_DepartmentUsers r JOIN  
                         (select a.*,b.Score_Bid from V_e_ScoreDeduck a join e_Score b on a.DeduckID=b.ID 
                         where Score_Bid>0 AND a.SignDate>='" + SDate + "' AND a.SignDate<='" + EDate + "') "
                       + "as V_e_ScoreDeduck  ON r.DogID=V_e_ScoreDeduck.AuthorID "
                       + "where V_e_ScoreDeduck.id is not null ";

            sql += " and r.areaCode like '" + AreaCode + "%'";
            if (DeptName != "-1")
            {
                sql += " and r.DepartName='" + DeptName + "'";

            }
            List<ProjectDeduckMXModel_ZBDLRY> list = dbContext.Database.SqlQuery<ProjectDeduckMXModel_ZBDLRY>(sql).ToList();
            return list;
        }

        public IList<ProjectDeduckMXModel_ZBDLRY> VerProjectDeduckMXForTitle(string AreaCode, int id)
        {
            string sql = @"SELECT tb.AuthorName,  tb.Name,  tb.LawActive, tb.ProjectName, tb.ExePerson, tb.ItemID, tb.SignDate,s.Score 
                           FROM e_Score s 
                           INNER JOIN 
                           (
                            SELECT e.ID AS ItemID,e.LawActive,d.ID,d.CorpDogID,d.classid,d.bFlag,d.AuthorName,d.Name,d.ProjectName,
                            d.ExePerson,d.SignDate,e.KHID FROM e_Deduck d INNER JOIN e_DeduckItem e ON(e.DeduckID=d.ID)
                           )tb
                           ON(tb.KHID=s.ID) WHERE s.ID = " + id + " and s.AreaCode like '"+AreaCode+"%'";


            List<ProjectDeduckMXModel_ZBDLRY> list = dbContext.Database.SqlQuery<ProjectDeduckMXModel_ZBDLRY>(sql).ToList();
            return list;
        }

        public IList<VerProjectAddMXModel> GetVerProjectAddMX(string AreaCode, string StartDate, string EndDate, string PersonIDCard,
                                                                string Name, int DogID, out int ListCount)
        {
            ////re_DepartmentUsers 替换 kh_ManageDeptList
            string sql = @"select 
                            a.ywid as ID, 
                            case a.ClassID when 2530 then 'e_OuterPrize' 
                            when 2540 then 'e_PrizePunish' 
                            when 2550 then 'e_ProjectPrizePunish' 
                            when 2570 then 'e_PrizeBZH' 
                            else convert(varchar(10),a.ClassID) 
                            end as Type ,
                            a.title,a.projectname,a.personName,
                            a.corpdogid,a.CorpName,a.CorpScore,
                            a.PersonScore,a.AuditDogID,a.DeptName
                            from tb_eKHData a join re_DepartmentUsers b on a.AuditDogID=b.DogID where AuditDate>='" + StartDate + "' and AuditDate<='" + EndDate + "' ";
            if (DogID != -1)
            {
                sql += " and corpdogid=" + DogID;
            }
            if (PersonIDCard != "-1")
            {
                sql += " and personid in (select ID from tb_Emp_Manager where IDCard='" + PersonIDCard + "')";
            }
            if (Name != "-1")
            {
                sql += " and (personName like '%" + Name + "%' or  projectname like '%" + Name + "%')";
            }
            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " AND b.areacode like '" + AreaCode + "%'";
            }
            

            var query = dbContext.Database.SqlQuery<VerProjectAddMXModel>(sql);

            IList<VerProjectAddMXModel> list = query.ToList();

            ListCount = list.Count;
            return list;
        }

        public IList<VerProjectDeduckMXModel> GetVerProjectDeduckMX(string AreaCode, string StartDate, string EndDate, string PersonIDCard,
                                                                string Name, int DogID, string Sign, out int ListCount)
        {
            //re_DepartmentUsers 替换 a_KhUserDefine
            string sql = "select b.deptid,b.DepartName as deptname,a.*,c.ID as ItemID,c.LawActive,c.Score from e_Deduck a join re_DepartmentUsers b on a.AuthorID =b.dogid join e_DeduckItem c on a.id=c.DeduckID where  bFlowStatus like '1%' ";
            sql += " and  SignDate>='" + StartDate + "' and SignDate<='" + EndDate + "' and CorpDogID=" + DogID;
            if (PersonIDCard != "-1")
            {
                sql += " and NameID in (select ID from tb_Emp_Manager where IDCard='" + PersonIDCard + "')";
            }
            if (!string.IsNullOrEmpty(Name))
            {
                sql += " and (name like '%" + Name + "%' or projectname  like '%" + Name + "%' or LawActive  like '%" + Name + "%') ";
            }
            if (Sign.ToLower().Equals("c"))
            {
                sql += " and EmpGuid='00000000-0000-0000-0000-000000000000'";
            }
            if (!string.IsNullOrEmpty(AreaCode))
            {
                sql += " AND b.areacode like '" + AreaCode + "%'";
            }

            var query = dbContext.Database.SqlQuery<VerProjectDeduckMXModel>(sql);

            IList<VerProjectDeduckMXModel> list = query.ToList();

            ListCount = list.Count;
            return list;
        }

        

      public IList<tb_Corp_Quality_Zbdlry> GetQualityListByCorpGuid(string userType, string corpGuid)
      {
          string sql=null;
          if (userType != "1")
          {
              sql = "SELECT * FROM  tb_Corp_Quality WHERE CorpUserGuid ='" + corpGuid + "' and FlowCode like '1,%' order by isMain desc,kindID asc";
          }
          else
          {
              sql = "SELECT * FROM  tb_Corp_Quality  where [QualityGuid] in (select QualityGuid from e_OuterPrize where [id]=" + corpGuid + ")";
          }
          List<tb_Corp_Quality_Zbdlry> list = dbContext.Database.SqlQuery<tb_Corp_Quality_Zbdlry>(sql).ToList();
          return list;
      }

      public IList<a_StructKind_ZBDL> GetSructKindList()
        {
            string sql = "SELECT * FROM a_StructKind";
            List<a_StructKind_ZBDL> list = dbContext.Database.SqlQuery<a_StructKind_ZBDL>(sql).ToList();
            return list;
        }


        public IList<a_zjjg_ZBDL> GetZjjgList()
        {
            string sql = "SELECT * FROM  a_zjjg order by code";
            List<a_zjjg_ZBDL> list = dbContext.Database.SqlQuery<a_zjjg_ZBDL>(sql).ToList();
            return list;
        }

        public IList<e_OuterPrize_ZBDL> GetOuterPrize(int id)
        {
            string sql = "select * from e_OuterPrize where ID="+id;
            List<e_OuterPrize_ZBDL> list = dbContext.Database.SqlQuery<e_OuterPrize_ZBDL>(sql).ToList();
            return list;
        }

        public string GetFlowStatusTitle(int id, int classID) 
        {
            string sql = "SELECT top 1 RegisterDate as title FROM e_FlowTable where b_FlowStatus=10 AND AttchedID=@id AND AttchedCLassID=@classID AND AuthorID in (SELECT DogID FROM T_Sys_Users WHERE RoleID = 7357 AND ClassID= 2000) order by id DESC";
            
            SqlParameter[] parameter ={
                new SqlParameter("@id",id),
                new SqlParameter("@classID",classID)
            };

            var list = dbContext.Database.SqlQuery<string>(sql, parameter).ToList();
            string result = "";
            if (list.Count > 0)
            {
                result = list[0];
            }
            return result;
         }

        public int GetOutPrizeCount(string strProjectID,string strCorpID,string strProjectArg,string strID)
        {
            string sql = string.Empty;
            if (strProjectID == "-1")//兼容以往，工程可能对应不上 
            {
                sql = @"SELECT count(*) FROM e_OuterPrize where CorpID=" + strCorpID + " ProjectName='" + strProjectArg + "' and bFlowStatus like '1%' and id<>" + strID;
            }
            else
            {
                sql = @"SELECT count(*) FROM e_OuterPrize where  CorpID=" + strCorpID + "ProjectID=" + strProjectArg + " and bFlowStatus like '1%' and id<>" + strID;
            }

            var list = dbContext.Database.SqlQuery<int>(sql).ToList();
            int count = 0;
            if (list.Count > 0)
            {
                count = list[0];
            }
            return count;
        }


        public string GetCorpZZCodeByGuid(string CorpGuid)
        {
            string sql = "select top 1 PassNum from tb_Corp_CompanyInfo where CorpUserGuid='" + CorpGuid + "'";

            var list = dbContext.Database.SqlQuery<string>(sql).ToList();
            string passNum = "";
            if (list.Count > 0)
            {
                passNum = list[0];
            }

            return passNum;
        }

        public string GetCorpZZgrade(string CorpGuid)
        {
            string sql = "select top 1 QualificationLevel from tb_Corp_CompanyInfo where CorpUserGuid='" + CorpGuid + "'";
            var list = dbContext.Database.SqlQuery<string>(sql).ToList();
            string qualificationLevel = string.Empty;
            if (list.Count > 0)
            {
                qualificationLevel = list[0];
            }

            return qualificationLevel;
 
        }



        public IList<T_Sys_Dictionary_Zbdl> GetDictionaryTable(string RootCode, string ParentSID)
        {
            string sql = "select * FROM T_Sys_Dictionary where RootCode=@RootCode and ParentSID=@ParentSID order by SortValue,code";
            SqlParameter[] parameter ={
                new SqlParameter("@RootCode",RootCode),
                new SqlParameter("@ParentSID",ParentSID)
            };

            List<T_Sys_Dictionary_Zbdl> list = dbContext.Database.SqlQuery<T_Sys_Dictionary_Zbdl>(sql, parameter).ToList();
            return list;
        }


        public IList<e_PrizeFlag_ZBDL> GetPrizeFlagList()
        {
            string sql = "SELECT * FROM e_PrizeFlag WHERE bflag = 0 ORDER BY sortIndex";
            List<e_PrizeFlag_ZBDL> list = dbContext.Database.SqlQuery<e_PrizeFlag_ZBDL>(sql).ToList();
            return list;
        }



        public IList<v_e_DeDuck_Zbdlry> GetDeDuck(int prizeID, int itemID)
        {
            string sql = "select * from v_e_DeDuck where ID=@ID and itemID=@itemID";
            SqlParameter[] parameter ={
                        new SqlParameter("@ID",prizeID),
                        new SqlParameter("@itemID",itemID)
                                     };
            List<v_e_DeDuck_Zbdlry> list = dbContext.Database.SqlQuery<v_e_DeDuck_Zbdlry>(sql, parameter).ToList();
            return list;
        }

        public IList<tb_Corp_CorpKind_Zbdl> GetDecZZ(string zzID)
        {
            string sql = "select * from tb_corp_corpkind";// where id=@zzID";
            if (!string.IsNullOrEmpty(zzID))
            {
                sql += " where id="+zzID;
               
            }

            List<tb_Corp_CorpKind_Zbdl> list = dbContext.Database.SqlQuery<tb_Corp_CorpKind_Zbdl>(sql).ToList();
            return list;
        }

        public IList<e_DeDuck_ZBDL> GetDeDuckTalbe(string id)
        {
            string sql = "select * from e_DeDuck where id=" + id;
            List<e_DeDuck_ZBDL> list = dbContext.Database.SqlQuery<e_DeDuck_ZBDL>(sql).ToList();
            return list;
        }


        public IList<e_DeduckItem_ZBDL> GetDeDuckItemTable(string id)
        {
            string sql = "select * from e_DeduckItem where DeduckID =" + id;
            List<e_DeduckItem_ZBDL> list = dbContext.Database.SqlQuery<e_DeduckItem_ZBDL>(sql).ToList();
            return list;
        }


        public IList<a_AreaInfo_Zbdl> GetAreaInfoTable(string areaCode)
        {
            string sql = "select * from a_AreaInfo where Code = @code";
            SqlParameter[] parameter = { new SqlParameter("@code",areaCode) };
            List<a_AreaInfo_Zbdl> list = dbContext.Database.SqlQuery<a_AreaInfo_Zbdl>(sql, parameter).ToList();
            return list;
        }



        public IList<V_e_SorcePublish_2006Public_ZBDL> GetKHResultTable(string calssid, string treeid, string bflag)
        {
            string sql = @"select  * from V_e_SorcePublish_2006Public where classid in (" + calssid + ") and TreeID in (" + treeid + ") order by Year_Sorce desc";
            List<V_e_SorcePublish_2006Public_ZBDL> list = dbContext.Database.SqlQuery<V_e_SorcePublish_2006Public_ZBDL>(sql).ToList();
            return list;
            
        }


        public IList<e_PrizePunish_ZBDL> GetNonProjectPrizeInfoByID(string myID)
        {
            string sql = "select * from e_PrizePunish where ID=" + myID;
            List<e_PrizePunish_ZBDL> list = dbContext.Database.SqlQuery<e_PrizePunish_ZBDL>(sql).ToList();
            return list;
        }

        public IList<NonProjectCheckTitle_ZBDL> GEtCheckTitleByKHID(string KHID)
        {
            string sql = @"SELECT e_Score.Title AS LittleTitle, e_ScoreType.Title AS BigTitle
                        FROM e_Score INNER JOIN e_ScoreType ON e_Score.TypeID = e_ScoreType.ID where  e_Score.ID="+KHID;
            List<NonProjectCheckTitle_ZBDL> list = dbContext.Database.SqlQuery<NonProjectCheckTitle_ZBDL>(sql).ToList();
            return list;
        }

        public string CorpZZCodeById(string id)
        {
            string sql = "select top 1 PassNum from tb_Corp_CompanyInfo where [id] in (select corpid from e_ProjectPrizePunish where [id]=" + id + ")";
            var list = dbContext.Database.SqlQuery<string>(sql).ToList();
            string passNum = "";
            if (list.Count > 0)
            {
                passNum = list[0];
            }



            return passNum;
        }


        public IList<e_ProjectPrizePunish_ZBDLRY> GetProjectPrizeInfoByID(string myID)
        {
            string sql = "select * from e_ProjectPrizePunish where ID="+myID;
            List<e_ProjectPrizePunish_ZBDLRY> list = dbContext.Database.SqlQuery<e_ProjectPrizePunish_ZBDLRY>(sql).ToList();
            return list;
        }


        public IList<e_PrizeBZH_ZBDL> GetEPrizeBZHList(string id)
        {
            string sql = "select * from e_PrizeBZH where ID=" + id;
            List<e_PrizeBZH_ZBDL> list = dbContext.Database.SqlQuery<e_PrizeBZH_ZBDL>(sql).ToList();
            return list;
        }


        public IList<tb_Corp_Quality_Zbdlry> getCorpZZInfo(string corpDogID)
        {
            string sql = "SELECT  T1.* FROM TB_CORP_QUALITY T1 INNER JOIN T_SYS_USERS T2 ON T1.CORPUSERGUID=T2.USERGUID WHERE T2.DOGID=" + corpDogID + "ORDER BY T1.ISMAIN DESC ";
            List<tb_Corp_Quality_Zbdlry> list = dbContext.Database.SqlQuery<tb_Corp_Quality_Zbdlry>(sql).ToList();
            return list;
        }
    }


    #region Before
    //          private ZBDL_Context zbdlcontext = new ZBDL_Context();
    //        public IList<ScoreAddSumByDepartmentNew> GetData(string AreaCode, DateTime StartDate, DateTime EndDate, string CorpTypeValue, string AddOrDeduck, out string xmlstr)
    //        {
    //            xmlstr = string.Empty;
    //            string selectSql = string.Empty;
    //            if (AddOrDeduck.Equals("deduck"))
    //            {
    //                selectSql = "exec SP_deduckSumByDepartmentNew @AreaCode,@SDate,@EDate";
    //            }
    //            else
    //            {
    //                selectSql = "exec SP_scoreAddSumByDepartmentNew @AreaCode,@SDate,@EDate";
    //            }

    //            try
    //            {
    //                SqlParameter param0 = new SqlParameter("@AreaCode", AreaCode);
    //                SqlParameter param1 = new SqlParameter("@SDate", "2009-11-01");
    //                SqlParameter param2 = new SqlParameter("@EDate", DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd"));

    //                if (StartDate.Year >= 2009)
    //                {
    //                    param1 = new SqlParameter("@SDate", StartDate.ToString("yyyy-MM-dd"));
    //                }

    //                if (EndDate > DateTime.Now.AddYears(-5))
    //                {
    //                    param2 = new SqlParameter("@EDate", EndDate.ToString("yyyy-MM-dd"));
    //                }

    //                SqlParameter[] parameters = { param0, param1, param2 };
    //                var list = zbdlcontext.Database.SqlQuery<ScoreAddSumByDepartmentNew>(selectSql, parameters).ToList();

    //                if (list != null)
    //                {
    //                    IList<ScoreAddSumByDepartmentNew> listsource = list;

    //                    if (StartDate.Year == 2009)
    //                    {
    //                        listsource = processZFC(list);
    //                    }
    //                    else if (StartDate.Year < 2009) //2009年以前的统计结果都静态的存在了a_KhUserDefine表里
    //                    {
    //                        listsource = processData(list);
    //                    }
    //                    CreateXML(listsource, CorpTypeValue, out xmlstr);
    //                    return list;
    //                }

    //                return list;
    //            }
    //            catch (Exception)
    //            {
    //                return null;
    //            }
    //        }

    //        public void CreateXML(IList<ScoreAddSumByDepartmentNew> list, string corptype, out string xmlstr)
    //        {
    //            DataTable dt = IListOut(list);

    //            StringBuilder sb = new StringBuilder();
    //            sb.Append("<chart showValues='1' canvasBgColor='D9E5F1' canvasBaseColor='D9E5F1' formatNumberScale='0' baseFont='宋体' baseFontSize='12'>");

    //            //横坐标
    //            sb.Append("<categories>");
    //            for (int i = 0; i < dt.Rows.Count; i++)
    //            {
    //                sb.Append("<category label='" + dt.Rows[i]["部门"].ToString() + "' />");
    //            }
    //            sb.Append("</categories>");

    //            //柱形图
    //            sb.Append("<dataset seriesName='加分记录数' >");
    //            for (int j = 0; j < dt.Rows.Count; j++)
    //            {
    //                sb.Append("<set value='" + dt.Rows[j]["" + corptype + ""].ToString() + "' />");
    //            }
    //            sb.Append("</dataset>");

    //            //走势图
    //            sb.Append("<dataset seriesname='加分分数' renderAs='Line' color='BBDA00' anchorSides='4' anchorRadius='5' anchorBgColor='BBDA00' anchorBorderColor='FFFFFF' anchorBorderThickness='2'>");
    //            for (int k = 0; k < dt.Rows.Count; k++)
    //            {
    //                sb.Append("<set value='" + dt.Rows[k]["" + corptype + "s"].ToString() + "' />");
    //            }
    //            sb.Append("</dataset>");
    //            sb.Append("<styles>");
    //            sb.Append("<definition><style type='font' name='CaptionFont' size='18' color='666666' /><style type='font' name='SubCaptionFont' bold='0' /></definition>");
    //            sb.Append("<application><apply toObject='caption' styles='CaptionFont' /><apply toObject='SubCaption' styles='SubCaptionFont' /></application>");
    //            sb.Append("</styles>");
    //            sb.Append("</chart>");

    //            xmlstr = sb.ToString();
    //        }

    //        /// <summary>
    //        /// 将IList转化为datatable
    //        /// </summary>
    //        /// <param name="ResList"></param>
    //        /// <returns></returns>
    //        public DataTable IListOut(IList<ScoreAddSumByDepartmentNew> ResList)
    //        {
    //            DataTable TempDT = new DataTable();

    //            //此处遍历IList的结构并建立同样的DataTable
    //            System.Reflection.PropertyInfo[] p = ResList[0].GetType().GetProperties();
    //            foreach (System.Reflection.PropertyInfo pi in p)
    //            {
    //                TempDT.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
    //            }

    //            for (int i = 0; i < ResList.Count; i++)
    //            {
    //                IList TempList = new ArrayList();
    //                //将IList中的一条记录写入ArrayList
    //                foreach (System.Reflection.PropertyInfo pi in p)
    //                {
    //                    object oo = pi.GetValue(ResList[i], null);
    //                    TempList.Add(oo);
    //                }

    //                object[] itm = new object[p.Length];
    //                //遍历ArrayList向object[]里放数据
    //                for (int j = 0; j < TempList.Count; j++)
    //                {
    //                    itm.SetValue(TempList[j], j);
    //                }
    //                //将object[]的内容放入DataTable
    //                TempDT.LoadDataRow(itm, true);
    //            }
    //            //返回DataTable
    //            return TempDT;
    //        }

    //        public IList<ScoreAddSumByDepartmentNew> processZFC(IList<ScoreAddSumByDepartmentNew> list)
    //        {
    //            foreach (var item in list)
    //            {
    //                int deptID = int.Parse(item.deptid.ToString());
    //                if (deptID == 4)//执法处
    //                {
    //                    //item.施工企业 = int.Parse(item.施工企业.ToString()) + 17;
    //                    //item.施工企业s = int.Parse(item.施工企业s.ToString()) + 51;
    //                    //item.监理企业 = int.Parse(item.监理企业.ToString()) + 9;
    //                    //item.监理企业s = int.Parse(item.监理企业s.ToString()) + 54;
    //                    item.建筑施工企业 = int.Parse(item.建筑施工企业.ToString()) + 17;
    //                    item.建筑施工企业s = int.Parse(item.建筑施工企业s.ToString()) + 51;
    //                    item.工程监理企业 = int.Parse(item.工程监理企业.ToString()) + 9;
    //                    item.工程监理企业s = int.Parse(item.工程监理企业s.ToString()) + 54;
    //                }
    //            }

    //            foreach (var item in list)
    //            {
    //                int deptID = int.Parse(item.deptid.ToString());
    //                if (deptID == 3)//建管处
    //                {
    //                    //item.施工企业 = int.Parse(item.施工企业.ToString()) + 280;
    //                    //item.施工企业s = int.Parse(item.施工企业s.ToString()) + 1047;
    //                    //item.监理企业 = int.Parse(item.监理企业.ToString()) + 25 + 18;
    //                    //item.监理企业s = int.Parse(item.监理企业s.ToString()) + 99 + 108;
    //                    //item.项目经理 = int.Parse(item.项目经理.ToString()) + 92;
    //                    //item.项目经理s = int.Parse(item.项目经理s.ToString()) + 811;
    //                    //item.项目总监 = int.Parse(item.项目总监.ToString()) + 18;
    //                    //item.项目总监s = int.Parse(item.项目总监s.ToString()) + 108;
    //                    item.建筑施工企业 = int.Parse(item.建筑施工企业.ToString()) + 280;
    //                    item.建筑施工企业s = int.Parse(item.建筑施工企业s.ToString()) + 1047;
    //                    item.工程监理企业 = int.Parse(item.工程监理企业.ToString()) + 25 + 18;
    //                    item.工程监理企业s = int.Parse(item.工程监理企业s.ToString()) + 99 + 108;
    //                }
    //            }
    //            return list;
    //        }

    //        public IList<ScoreAddSumByDepartmentNew> processData(IList<ScoreAddSumByDepartmentNew> list)
    //        {
    //            try
    //            {
    //                foreach (var item in list)
    //                {
    //                    int deptID = int.Parse(item.deptid.ToString());
    //                    a_KhUserDefine userdefine = zbdlcontext.UserDefine.Where(u => u.treeID == 2001 && u.deptID == deptID).FirstOrDefault();

    //                    if (userdefine != null)
    //                    {
    //                        int sum = 0;
    //                        item.建筑施工企业 = int.Parse(item.建筑施工企业.ToString()) + int.Parse(userdefine.Count_sg.ToString());
    //                        item.工程监理企业 = int.Parse(item.工程监理企业.ToString()) + int.Parse(userdefine.Count_jl.ToString());

    //                        item.建筑施工企业s = int.Parse(item.建筑施工企业s.ToString()) + int.Parse(userdefine.Score_sg.ToString());
    //                        item.工程监理企业s = int.Parse(item.工程监理企业s.ToString()) + int.Parse(userdefine.Score_jl.ToString());

    //                        item.建设开发企业 = int.Parse(item.建设开发企业.ToString()) + int.Parse(userdefine.Count_jsdw.ToString());
    //                        item.建设开发企业s = int.Parse(item.建设开发企业s.ToString()) + int.Parse(userdefine.Score_jsdw.ToString());
    //                        item.招标代理机构 = int.Parse(item.招标代理机构.ToString()) + int.Parse(userdefine.Count_zbdl.ToString());
    //                        item.招标代理机构s = int.Parse(item.招标代理机构s.ToString()) + int.Parse(userdefine.Score_zbdl.ToString());
    //                        item.造价咨询企业 = int.Parse(item.造价咨询企业.ToString()) + int.Parse(userdefine.Count_zjzx.ToString());
    //                        item.造价咨询企业s = int.Parse(item.造价咨询企业s.ToString()) + int.Parse(userdefine.Score_zjzx.ToString());
    //                    }
    //                }
    //            }
    //            catch (System.Exception e)
    //            {

    //            }
    //            return list;
    //        }

    //        public IList<SumVer> GetSumVerByCorp(DateTime SDate, DateTime EDate)
    //        {
    //            SqlParameter[] parameters ={
    //                new SqlParameter("@SDate",SDate),
    //                new SqlParameter("@EDate",EDate)
    //            };
    //            var list = zbdlcontext.Database.SqlQuery<SumVer>("EXEC P_SumVerByCorp @SDate,@EDate", parameters).ToList();
    //            return list;
    //        }

    //        public IList<SumVer> GetSumVerByPerson(DateTime SDate, DateTime EDate)
    //        {
    //            SqlParameter[] parameters ={
    //                new SqlParameter("@SDate",SDate),
    //                new SqlParameter("@EDate",EDate)
    //            };
    //            var list = zbdlcontext.Database.SqlQuery<SumVer>("EXEC P_SumVerByPerson @SDate,@EDate", parameters).ToList();
    //            return list;
    //        }

    //        public IList<DeduckSumByDepartmentNew> GetDataDeduck(string AreaCode, DateTime StartDate, DateTime EndDate, string CorpTypeValue, out string xmlstr)
    //        {
    //            xmlstr = string.Empty;
    //            string selectSql = string.Empty;
    //            selectSql = "exec SP_deduckSumByDepartmentNew @AreaCode,@SDate,@EDate";

    //            DataSet ds = new DataSet();

    //            //SqlConnection conn = new SqlConnection(ConnDataBase.ConnectionString);
    //            try
    //            {
    //                //string CommandText = @"P_deduckSumByDepartmentNew";
    //                //SqlDataAdapter adapter = new SqlDataAdapter();
    //                //adapter.SelectCommand = new SqlCommand(CommandText, conn);
    //                //adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    //                SqlParameter param0 = new SqlParameter("@AreaCode", AreaCode);
    //                SqlParameter param1 = new SqlParameter("@SDate", "2009-11-01");
    //                SqlParameter param2 = new SqlParameter("@EDate", DateTime.Now.AddYears(-5).ToString("yyyy-MM-dd"));

    //                if (StartDate.Year >= 2009)
    //                {
    //                    param1 = new SqlParameter("@SDate", StartDate.ToString("yyyy-MM-dd"));
    //                }

    //                if (EndDate > DateTime.Now.AddYears(-5))
    //                {
    //                    param2 = new SqlParameter("@EDate", EndDate.ToString("yyyy-MM-dd"));
    //                }

    //                SqlParameter[] parameters = { param0, param1, param2 };

    //                //adapter.Fill(ds, "list");
    //                var list = zbdlcontext.Database.SqlQuery<DeduckSumByDepartmentNew>(selectSql, parameters).ToList();

    //                if (list != null)
    //                {
    //                    IList<DeduckSumByDepartmentNew> listsource = list;
    //                    CreateXMLDeduck(listsource, CorpTypeValue, out xmlstr);
    //                    //CreateXML(ds.Tables[0], CorpTypeValue);
    //                    return list;
    //                }
    //                return list;
    //            }
    //            catch (Exception ex)
    //            {
    //                return null;
    //            }
    //        }

    //        public void CreateXMLDeduck(IList<DeduckSumByDepartmentNew> list, string corptype, out string xmlstr)
    //        {
    //            DataTable dt = IListOutDeduck(list);

    //            StringBuilder sb = new StringBuilder();
    //            sb.Append("<chart showValues='1' showColumnShadow='0'  formatNumberScale='0' baseFont='宋体' baseFontSize='12'>");

    //            //横坐标
    //            sb.Append("<categories>");
    //            for (int i = 0; i < dt.Rows.Count; i++)
    //            {
    //                sb.Append("<category label='" + dt.Rows[i]["部门"].ToString() + "' />");
    //            }
    //            sb.Append("</categories>");
    //            //走势图
    //            sb.Append("<dataset seriesname='扣分分数' parentYAxis='S' renderAs='Line'  anchorSides='4' anchorRadius='5' anchorBgColor='AFD8F8' anchorBorderColor='FFFFFF' anchorBorderThickness='2'>");
    //            for (int k = 0; k < dt.Rows.Count; k++)
    //            {
    //                sb.Append("<set value='" + dt.Rows[k]["" + corptype + "s"].ToString() + "' />");
    //            }
    //            sb.Append("</dataset>");
    //            sb.Append("<styles>");
    //            sb.Append("<definition><style type='font' name='CaptionFont' size='18' color='666666' /><style type='font' name='SubCaptionFont' bold='0' /></definition>");
    //            sb.Append("<application><apply toObject='caption' styles='CaptionFont' /><apply toObject='SubCaption' styles='SubCaptionFont' /></application>");
    //            sb.Append("</styles>");

    //            //柱形图
    //            sb.Append("<dataset seriesName='扣分记录数' >");
    //            for (int j = 0; j < dt.Rows.Count; j++)
    //            {
    //                sb.Append("<set value='" + dt.Rows[j]["" + corptype + ""].ToString() + "' />");
    //            }
    //            sb.Append("</dataset>");


    //            sb.Append("</chart>");

    //            xmlstr = sb.ToString();
    //        }

    //        public DataTable IListOutDeduck(IList<DeduckSumByDepartmentNew> ResList)
    //        {
    //            DataTable TempDT = new DataTable();

    //            //此处遍历IList的结构并建立同样的DataTable
    //            System.Reflection.PropertyInfo[] p = ResList[0].GetType().GetProperties();
    //            foreach (System.Reflection.PropertyInfo pi in p)
    //            {
    //                TempDT.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
    //            }

    //            for (int i = 0; i < ResList.Count; i++)
    //            {
    //                IList TempList = new ArrayList();
    //                //将IList中的一条记录写入ArrayList
    //                foreach (System.Reflection.PropertyInfo pi in p)
    //                {
    //                    object oo = pi.GetValue(ResList[i], null);
    //                    TempList.Add(oo);
    //                }

    //                object[] itm = new object[p.Length];
    //                //遍历ArrayList向object[]里放数据
    //                for (int j = 0; j < TempList.Count; j++)
    //                {
    //                    itm.SetValue(TempList[j], j);
    //                }
    //                //将object[]的内容放入DataTable
    //                TempDT.LoadDataRow(itm, true);
    //            }
    //            //返回DataTable
    //            return TempDT;
    //        }


    //        public IList<SGandJLInfo> GetSGandJLInfo(string Code)
    //        {
    //            string sqlStr = string.Format(@"
    //                SELECT
    //                    bzk.ID,bzk.Code,hpc.CorpType,hpc.CorpName,tq.DetailName, 
    //                    tq.QualityCode, kc.SafeLicenceNo,kc.LicenceDept ,
    //                    '' as Contract, '' as BeiAnJiGuan, hpm.ManagerName,
    //                    hpm.ManagerCode,tm.Tel,tm.ID as EMID,hpc.CorpUserGuid
    //                FROM h_projectBzk_New as bzk
    //                LEFT JOIN h_ProjectCorp as hpc on bzk.ID = hpc.ProjectId
    //                LEFT JOIN tb_Corp_Quality as tq on hpc.CorpUserGuid  = tq.CorpUserGuid AND tq.IsMain = 1
    //                LEFT JOIN kh_corptable as kc ON hpc.CorpUserGuid  = kc.CorpUserGuid
    //                LEFT JOIN h_ProjectManager as hpm ON hpm.ProjectCorpId  = hpc.Id
    //                LEFT JOIN tb_emp_manager as tm ON tm.ID = hpm.ManagerId 
    //                WHERE bzk.bKind = 2 AND  (hpc.CorpType2 = 0 or hpc.CorpType2 is null) and hpc.CorpType in (15,42)
    //                AND hpm.ManagerType IN (0,2) AND bzk.IsDel = 0 AND hpc.IsDel = 0 AND hpm.IsDel = 0 
    //                AND bzk.Code like '{0}%'", Code);

    //            var list = zbdlcontext.Database.SqlQuery<SGandJLInfo>(sqlStr).ToList();
    //            return list;
    //        }

    //        public IList<h_ProjectCorp> GetSGProjectList(int CorpType, int ProjectID)
    //        {
    //            string sqlStr = string.Format(@"SELECT
    //                            hpc.*
    //                        FROM h_ProjectCorp as hpc
    //                        LEFT JOIN tb_Corp_Quality as tq on hpc.CorpUserGuid = tq.CorpUserGuid AND tq.IsMain = 1
    //                        LEFT JOIN kh_corptable as kc ON hpc.CorpUserGuid = kc.CorpUserGuid
    //                        LEFT JOIN h_ProjectManager as hpm ON hpm.ProjectId = hpc.ProjectId AND hpm.ProjectCorpId = hpc.Id
    //                        WHERE hpc.CorpType = 15
    //                        AND hpc.CorpType2 = {0}
    //                        AND hpm.ManagerType = 0
    //                        AND hpc.ProjectId = {1}", CorpType, ProjectID);

    //            var list = zbdlcontext.Database.SqlQuery<h_ProjectCorp>(sqlStr).ToList();
    //            return list;
    //        }

    //        public IList<VerProject> GetVerProjectList(string CorpName, string ProjectName, string ManagerName,
    //            int ProjectType, string AreaCode, DateTime BeginDate, int VisualProgress, int Dogid, string CorpUserGuid,
    //            int bStatus,int pageIndex, int pageSize, out int dataCount)
    //        {
    //            SqlParameter[] param = { 
    //                new SqlParameter("@CorpName",CorpName),
    //                new SqlParameter("@ProjectName",ProjectName),
    //                new SqlParameter("@ManagerName",ManagerName),
    //                new SqlParameter("@ProjectType",ProjectType),
    //                new SqlParameter("@AreaCode",AreaCode),
    //                new SqlParameter("@BeginDate",BeginDate),
    //                new SqlParameter("@VisualProgress",VisualProgress)
    //            };
    //            string sqlStr = @"sp_h_projectbzk_new_GetProjectInfoList @CorpName,@ProjectName,@ManagerName,
    //                @ProjectType,@AreaCode,@BeginDate,@VisualProgress";
    //            var query = zbdlcontext.Database.SqlQuery<VerProject>(sqlStr, param);
    //            List<VerProject> list = query.ToList();
    //            dataCount = list.Count;

    //            return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
    //        }

    //        public IList<int> GetDeduckList(string ProjectName, string CorpUserGuid)
    //        {
    //            string sqlStr = string.Format(@"
    //                SELECT SUM(i.Score) AS koufen 
    //                FROM dbo.e_Deduck d JOIN dbo.e_DeduckItem i  
    //                ON d.ID=i.DeduckID 
    //                WHERE d.ProjectName='{0}' 
    //                and   d.CorpUserGuid='{1}'  
    //                GROUP BY d.ProjectName, d.CorpUserGuid", ProjectName, CorpUserGuid);

    //            var list = zbdlcontext.Database.SqlQuery<int>(sqlStr).ToList();
    //            return list;
    //        }

    //        public IList<int> GetTotalCorpScoreList(string ProjectName, string CorpUserGuid, string DogID)
    //        {
    //            string sqlStr = string.Format(@"
    //                SELECT SUM(Total_CorpScore) AS  TotalCorpScore
    //                FROM dbo.e_ProjectPrizePunish
    //                WHERE projectName='{0}' and CorpUserGuid='{1}'
    //                GROUP BY ProjectName  
    //                UNION
    //                SELECT SUM(Total_CorpScore) AS TotalCorpScore
    //                FROM dbo.e_PrizeBZH
    //                WHERE projectName= '{0}' and CorpDogID='{2}'
    //                GROUP BY ProjectName", ProjectName, CorpUserGuid, DogID);
    //            var list = zbdlcontext.Database.SqlQuery<int>(sqlStr).ToList();
    //            return list;
    //        }

    //        public string GetUserCorpIDByDog(string DogID)
    //        {
    //            int dogid = 0;
    //            int.TryParse(DogID, out dogid);
    //            var CorpID = (from us in zbdlcontext.T_Sys_Users
    //                          where us.DogID == dogid
    //                          select us.CorpID).FirstOrDefault();
    //            return CorpID == null ? "" : CorpID.ToString();
    //        }

    //        public IList<StatisticsCorpInfo> GetStatisticsCorpList(string projectName, string corpName, string AwardType,
    //            int DogID, string Date, int pageIndex, int pageSize, out int dataCount)
    //        {
    //            string sqlStr = @"
    //                SELECT
    //                    classid,ProjectName,ProjectID,CorpName,CorpDogID,CorpID,
    //                    BuildAreas AS Areas,Cost,ID,'P' AS SIGN,RegisterDate,
    //                    (SELECT SUM(Score) FROM e_Score WHERE ID=r.KHID1 OR ID=r.KHID2) Score,0 AS deScore 
    //                FROM e_ProjectPrizePunish r
    //                UNION ALL
    //                SELECT
    //                    classid,ProjectName,ProjectID,CorpName,CorpDogID,CorpID,
    //                    CONVERT(VARCHAR,AuditArea) AS Areas,CONVERT(VARCHAR,AuditCost) AS Cost,ID,'O' AS SIGN,RegisterDate,
    //                    (PersonScore+CorpScore)Score,0 AS deScore
    //                FROM e_OuterPrize o
    //                UNION ALL
    //                SELECT
    //                    (SELECT CONVERT(VARCHAR,classid) FROM  dbo.tb_Corp_CompanyInfo WHERE ID=p.CorpID)AS classid,
    //                    ProjectName,ProjectID,CorpName,CorpDogID,CorpID,'0' AS Areas,'0' AS cost,ID,'B' AS SIGN,RegisterDate,
    //                    (SELECT SUM(Score) FROM e_Score WHERE ID=p.KHID1 OR ID=p.KHID2) score,0 AS descroe
    //                FROM e_PrizeBZH p
    //                UNION ALL
    //                SELECT
    //                    classid, ProjectName,ProjectID,(SELECT tc.CorpName FROM  tb_Corp_CompanyInfo tc WHERE ID=d.CorpID)AS CorpName,
    //                    CorpDogID,CorpID,'0' AS Areas,'0' AS cost,ID,'D' AS SIGN,RegisterDate,0 AS Score,
    //                    (SELECT SUM(Score) FROM e_DeduckItem WHERE DeduckID=d.ID ) AS deScore 
    //                FROM e_Deduck d
    //                ORDER BY ProjectName DESC";

    //            if (projectName != "" || corpName != "" || AwardType != "" || DogID > 0)
    //            {
    //                int? CorpID = 0;
    //                if (DogID > 0)
    //                {
    //                    CorpID = (from us in zbdlcontext.T_Sys_Users
    //                              where us.DogID == DogID
    //                              select us.CorpID).FirstOrDefault();
    //                    CorpID = CorpID ?? 0;
    //                }
    //                sqlStr = @"select * from (" + sqlStr.Substring(0, sqlStr.IndexOf("ORDER")) + ") tb where 1=1 ";
    //                if (!string.IsNullOrEmpty(projectName))
    //                {
    //                    sqlStr += " and ProjectName like '%" + projectName + "%'";
    //                }
    //                if (!string.IsNullOrEmpty(corpName))
    //                {
    //                    sqlStr += " and CorpName like '%" + corpName + "%'";
    //                }
    //                if (!string.IsNullOrEmpty(AwardType))
    //                {
    //                    sqlStr += " and SIGN='" + AwardType.ToLower() + "'";
    //                }
    //                if (CorpID != 0)
    //                {
    //                    sqlStr += " and CorpID=" + CorpID;
    //                }
    //                sqlStr += " and RegisterDate like'" + Date + "%'  ORDER BY ProjectName DESC";
    //            }

    //            var query = zbdlcontext.Database.SqlQuery<StatisticsCorpInfo>(sqlStr);
    //            try
    //            {
    //                IList<StatisticsCorpInfo> list = query.ToList();
    //                dataCount = list.Count;
    //                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
    //            }
    //            catch (Exception ex)
    //            {
    //                dataCount = 0;
    //                return new List<StatisticsCorpInfo>() { 
    //                    new StatisticsCorpInfo(){
    //                        classid="11111"
    //                        ,ProjectName="综合楼"
    //                        ,ProjectID=-1
    //                        ,CorpName=ex.Message
    //                        ,CorpDogID=126547
    //                        ,CorpID=421
    //                        ,Areas="0"
    //                        ,Cost="115.62"
    //                        ,ID=11775
    //                        ,SIGN="O"
    //                        ,RegisterDate= DateTime.Now
    //                        ,Score=null
    //                        ,deScore=null
    //                    }
    //                };
    //            }
    //        }

    //        public IList<AddScoreClass> GetAddScoreClass(string AreaCode, DateTime SDate, DateTime EDate, string Type, string DeptName)
    //        {
    //            SqlParameter[] parameter ={
    //                //new SqlParameter("@AreaCode",AreaCode),
    //                new SqlParameter("@SDate",SDate),
    //                new SqlParameter("@EDate",EDate),
    //                new SqlParameter("@Role",Type),
    //                new SqlParameter("@DeptName",DeptName)
    //            };

    //            string sql = string.Format("EXEC P_scoreAddSumByClass '{0}','{1}','{2}','{3}'", SDate, EDate, Type, DeptName);

    //            var list = zbdlcontext.Database.SqlQuery<AddScoreClass>(sql).ToList();
    //            return list;
    //        }

    //        public string GetTranTypeTitle(string AreaCode, string RoleID)
    //        {
    //            string sql = "select roleName as title from e_SckhVerSumClass where roleid=@RoleID";

    //            SqlParameter[] parameter ={
    //                new SqlParameter("@AreaCode",AreaCode),
    //                new SqlParameter("@RoleID",RoleID)
    //            };

    //            var list = zbdlcontext.Database.SqlQuery<string>(sql, parameter).ToList();
    //            string result = "";
    //            if (list.Count > 0)
    //            {
    //                result = list[0];
    //            }
    //            return result;
    //        } 
    #endregion

    //public class AddScoreClass
    //{
    //    public int PrizeFlag { set; get; }
    //    public string AddClassName { set; get; }
    //    public Int16 sortIndex { set; get; }
    //    public int PrizeNum { set; get; }
    //    public double PrizeSumScore { set; get; }
    //    public string Title { set; get; }
    //}

    //public class StatisticsCorpInfo
    //{
    //    public string classid { set; get; }
    //    public string ProjectName { set; get; }
    //    public int? ProjectID { set; get; }
    //    public string CorpName { set; get; }
    //    public int CorpDogID { set; get; }
    //    public int? CorpID { set; get; }
    //    public string Areas { set; get; }
    //    public string Cost { set; get; }
    //    public int ID { set; get; }
    //    public string SIGN { set; get; }
    //    public DateTime RegisterDate { set; get; }
    //    public double? Score { set; get; }
    //    public double? deScore { set; get; }
    //}

    //public class VerProject
    //{
    //    public string Code { set; get; }
    //    public string Title { set; get; }
    //    public string Owner { set; get; }
    //    public int? ID { set; get; }
    //    public int? ProjectID { set; get; }
    //    public Guid? CorpUserGuid { set; get; }
    //}

    //public class ScoreAddSumByDepartmentNew
    //{
    //    public string 部门 { get; set; }
    //    public int deptid { get; set; }
    //    public string areaCode { get; set; }
    //    public decimal 建筑施工企业 { get; set; }
    //    public decimal 工程监理企业 { get; set; }
    //    public decimal 建筑施工企业s { get; set; }
    //    public decimal 工程监理企业s { get; set; }
    //    public decimal 建设开发企业 { get; set; }
    //    public decimal 建设开发企业s { get; set; }
    //    public decimal 招标代理机构 { get; set; }
    //    public decimal 招标代理机构s { get; set; }
    //    public decimal 造价咨询企业 { get; set; }
    //    public decimal 造价咨询企业s { get; set; }
    //    public decimal 勘察设计企业 { get; set; }
    //    public decimal 勘察设计企业s { get; set; }
    //    public decimal 市政施工企业 { get; set; }
    //    public decimal 市政施工企业s { get; set; }
    //    public decimal 预拌混凝土 { get; set; }
    //    public decimal 预拌混凝土s { get; set; }
    //    public decimal 建筑装饰企业 { get; set; }
    //    public decimal 建筑装饰企业s { get; set; }
    //    public decimal 项目经理 { get; set; }
    //    public decimal 项目总监 { get; set; }
    //    public decimal 造价专业人员 { get; set; }
    //    public decimal 项目经理s { get; set; }
    //    public decimal 项目总监s { get; set; }
    //    public decimal 造价专业人员s { get; set; }
    //    public decimal 合计 { get; set; }
    //    public int orderbyid { get; set; }
    //}

    //public class DeduckSumByDepartmentNew
    //{
    //    public string 部门 { get; set; }
    //    public int deptid { get; set; }
    //    public string areaCode { get; set; }
    //    public int 施工企业 { get; set; }
    //    public double 施工企业s { get; set; }
    //    public int 项目经理 { get; set; }
    //    public double 项目经理s { get; set; }
    //    public int 造价专业人员 { get; set; }
    //    public double 造价专业人员s { get; set; }
    //    public int 监理企业 { get; set; }
    //    public double 监理企业s { get; set; }
    //    public int 项目总监 { get; set; }
    //    public double 项目总监s { get; set; }
    //    public int 勘察设计单位 { get; set; }
    //    public double 勘察设计单位s { get; set; }
    //    public int 建设单位 { get; set; }
    //    public double 建设单位s { get; set; }
    //    public int 招标代理机构 { get; set; }
    //    public double 招标代理机构s { get; set; }
    //    public int 造价咨询企业 { get; set; }
    //    public double 造价咨询企业s { get; set; }
    //    public int 劳务分包企业 { get; set; }
    //    public double 劳务分包企业s { get; set; }
    //    public int 代建单位 { get; set; }
    //    public double 代建单位s { get; set; }
    //    public int 预拌混凝土企业 { get; set; }
    //    public double 预拌混凝土企业s { get; set; }
    //    public int 检测机构 { get; set; }
    //    public double 检测机构s { get; set; }
    //    public int 合计 { get; set; }
    //    public int orderbyid { get; set; }

    //}


}
